Power BI Spain Users Group

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only

Nested filters same table - measure in calendar

  • 1.  Nested filters same table - measure in calendar

    Posted Nov 15, 2021 11:57 AM
    Good afternoon everyone!
    Right now, I am stuck with this problem, and I am wondering if you are able to help.
    Let me explain the problem in detail.
    I have a table like this one: 
    business_id bookkeeping_id campaign_name cost created_date
    01 02 logikaline 5 01/10/2021
    02 5 01/10/2021
    03 02 logikaline 5 01/11/2021
    04 5 01/10/2021
    05 04 logikaline 5 01/11/2021
    06 04 logikaline 5 01/12/2021
    I'd like to create a measure where I keep the cost from the business_id that is coming from specific bookkeepers. In this example, I would like to get the cost from the business_id = 01,03,05,06
    So I have done the following measure:
    S&P_comisiones_logikaline =//Here I am filtering the table to catch the businesses_id that are bookkeeping 
    VAR filtered_table =
        CALCULATETABLE(
            business_latest_completed,
            AND (
                business_latest_completed[campaign_name] = "Logikaline",
                business_latest_completed[type] = "bookkeeping"
            ),ALL(business_latest_completed)
        )//Here I am getting the list of values that are businesses that work as bookkeepers, in my example would be 02 and 04
    VAR business_id_logikaline_bookkeepers =
        CALCULATETABLE (
            VALUES(business_latest_completed[id]),
            filtered_table
        )//Here I am filtering the same table matching those business_id that are in the column bookkeeping_id. So I am looking for those rows where the bookkeeping_id is equal to 02 and 04 (following my example). And I also filter the column type = "negocio"
    VAR business_from_logikaline_bookkeepers =
        CALCULATETABLE(
            business_latest_completed,
            AND (
                business_latest_completed[bookkeeper_id]
                    IN business_id_logikaline_bookkeepers,
                business_latest_completed[type] = "negocio"
            )
        )//Once I have detected those business_id that come from specific bookeeppers, I use a SUMX to get the desire result. 
    VAR summing_campaing_cost =
        CALCULATE (
            SUMX (
                business_from_logikaline_bookkeepers,
                business_latest_completed[coste_acumulado]
            )
        )
    RETURN
    summing_campaing_cost
     
    So in the beginning, the measure seems to work fine, because if I introduce the measure inside a table per each id, it retrieves the business_id that has the specific conditions and retrieves the amount as you can see in the following picture.
     
    The thing is that I need to use this measure by month_year, but it doesn`t work properly. If I put the calculated column that I use inside SUMX in the above measure, it works fine. 
    Furthermore, I think that the problem comes because the column "coste_acumulado" is a calculated column. Because I have tried the measure putting a column that comes inside the table (it's not calculated) and the result inside the month_year works fine.  Let me show you how it is created.
    comissions_days (calculated column) =
    VAR today_day = INT(TODAY())
    VAR start_day = INT(business_latest_completed[start_date_date])
    VAR end_day = INT(IF(ISBLANK(business_latest_completed[end_date_date]),today_day,business_latest_completed[end_date_date]))
    VAR result =
        IF( end_day < today_day,
        IF(ISBLANK(business_latest_completed[start_date_date]),0,
        end_day - start_day),
        IF(ISBLANK(business_latest_completed[start_date_date]),0,
        today_day - start_day)
        )
    RETURN
    IF(result < 720,result,0)
    -----------------------------------------------------------------------
    logikaline_percentages (calculated column) =
        IF (
            business_latest_completed[comissions_days] <= 365,
            0.25,
            IF (
                365 < business_latest_completed[comissions_days]
                    && business_latest_completed[comissions_days] <= 730,
                0.15,
                IF (
                    730 < business_latest_completed[comissions_days]
                        && business_latest_completed[comissions_days] <= 1395,
                    0.05,
                    0
                )
            )
        )
    ------------------------------------------------
    price (it comes with the table) 
    ---------------------------------------------------
    coste_acumulado (calculated column) = business_latest_completed[logikaline_percentages] * business_latest_completed[price]
    ------------------------------------------
    Another thing you may need to know how is the relationship with the calendar.  Nothing weird.
    Thank you very much in advance.
    Best regards,
    Marcos.


    ------------------------------
    Marcos Martin
    Business Analyst
    ------------------------------