Forum Discussion

vivek_babu's avatar
vivek_babu
Icon for Helper II rankHelper II
1 year ago
Solved

Dynamic measure calculation using selected date from the slicer

Hi,   I am trying to create a flag based in the user date selection from the slicer. I have a disconnected date table and then i have my main table. I have a date slicer and the date comes from the...
Need Help
  • johnt75's avatar
    johnt75
    1 year ago

    I think the issue might be the ALL, which you don't include in the COUNTROWS version of the measure. Removing that might fix the problem.

    I would point out that it is best practice not to filter entire tables but only to filter the columns you need, so you could use

    Test Sumx =
VAR SelectedDate = [Selected_Date]
RETURN
    SUMX (
        FILTER (
            SELECTCOLUMNS (
                VW_CONS_PC_AR_LOAD,
                VW_CONS_PC_AR_LOAD[ac_doc_status],
                VW_CONS_PC_AR_LOAD[pstng_date],
                VW_CONS_PC_AR_LOAD[PC_DBCR_GC],
                VW_CONS_PC_AR_LOAD[CLEAR_DATE]
            ),
            ( VW_CONS_PC_AR_LOAD[ac_doc_status] = "O"
                && VW_CONS_PC_AR_LOAD[pstng_date] <= SelectedDate
                && VW_CONS_PC_AR_LOAD[PC_DBCR_GC] <> 0 )
                || ( VW_CONS_PC_AR_LOAD[ac_doc_status] = "C"
                && VW_CONS_PC_AR_LOAD[pstng_date] <= SelectedDate
                && VW_CONS_PC_AR_LOAD[CLEAR_DATE] >= SelectedDate
                && VW_CONS_PC_AR_LOAD[PC_DBCR_GC] <> 0 )
        ),
        1
    )

    Finally, your Snowflake query has a GROUP BY clause, which neither of the above measures does, so that will return 1 row per combination of posting date and clearing date, whereas the DAX could return multiple rows.

wardy912's avatar
wardy912
Icon for Super User rankSuper User
1 year ago

Hi vivek_babu 

 

 You should use FILTER to evaulate each row of your main table and apply the logic correctly. Try this:

 

Test Flag =
VAR SelectedDate = [User_Selected_Date]
RETURN
CALCULATE(
    COUNTROWS(
        FILTER(
            VW_CONS_PC_AR_LOAD,
            NOT ISBLANK(SelectedDate) &&
            (
                (VW_CONS_PC_AR_LOAD[ac_doc_status] = "0" && VW_CONS_PC_AR_LOAD[pstng_date] <= SelectedDate) ||
                (VW_CONS_PC_AR_LOAD[ac_doc_status] = "C" && VW_CONS_PC_AR_LOAD[pstng_date] <= SelectedDate && VW_CONS_PC_AR_LOAD[clear_date] >= SelectedDate)
            )
        )
    )
)

 

I hope this helps, please give a thumbs up and mark as solved if it does, thanks!

 

  • vivek_babu's avatar
    vivek_babu
    Icon for Helper II rankHelper II
    1 year ago

    Hi wardy912 

     

    Thansk for the response,

     

    This measure logic is not working.  This is the snowflake query,
    SELECT pstng_date, clear_date
    FROM DP_FDW.VW_CONS_PC_AR_LOAD
    WHERE
    (
    ac_doc_status = 'O' AND pstng_date <= '2022-12-01'
    )
    OR (
    ac_doc_status = 'C' AND pstng_date <= '2022-12-01' AND CLEAR_DATE >= '2022-12-01'
    )
    GROUP BY pstng_date, clear_date
    ORDER BY pstng_date
    I am getting 13202 rows as the output. Only difference here the date is hardcoded but in Power BI it should be dynamic and coming from user selection. The above measure logic is not working i am getting less rows as the ouput(2123 rows only)  and some dates are missing as compared to snowflake result 

     

    Regards

  • vivek_babu's avatar
    vivek_babu
    Icon for Helper II rankHelper II
    1 year ago

    Hi wardy912 ,

     

    The countrows logic is working and i am getting the correct rows based on the user date selection. But i want the filteredrows in a flag value like 1 and i will use this flag in my table visual to further filter the data. This is the countrows measure which works,

     

    Test countrow =
    VAR SelectedDate = [Selected_Date]
    RETURN
        CALCULATE (
            COUNTROWS (VW_CONS_PC_AR_LOAD),
            FILTER (
                VW_CONS_PC_AR_LOAD,
                (
                    VW_CONS_PC_AR_LOAD[ac_doc_status] = "O" &&
                    VW_CONS_PC_AR_LOAD[pstng_date] <= SelectedDate &&
                    VW_CONS_PC_AR_LOAD[PC_DBCR_GC] <> 0
                )
                ||
                (
                    VW_CONS_PC_AR_LOAD[ac_doc_status] = "C" &&
                    VW_CONS_PC_AR_LOAD[pstng_date] <= SelectedDate &&
                    VW_CONS_PC_AR_LOAD[CLEAR_DATE] >= SelectedDate &&
                    VW_CONS_PC_AR_LOAD[PC_DBCR_GC] <> 0
                )
            )
        )
    I tried to use sumx and change the measure but that is returning me blank value for the flag 1,
    Test Sumx =
    VAR SelectedDate = [Selected_Date]
    RETURN
        SUMX(
            FILTER (
                ALL(VW_CONS_PC_AR_LOAD),
                (
                    VW_CONS_PC_AR_LOAD[ac_doc_status] = "O" &&
                    VW_CONS_PC_AR_LOAD[pstng_date] <= SelectedDate &&
                    VW_CONS_PC_AR_LOAD[PC_DBCR_GC] <> 0
                )
                ||
                (
                    VW_CONS_PC_AR_LOAD[ac_doc_status] = "C" &&
                    VW_CONS_PC_AR_LOAD[pstng_date] <= SelectedDate &&
                    VW_CONS_PC_AR_LOAD[CLEAR_DATE] >= SelectedDate &&
                    VW_CONS_PC_AR_LOAD[PC_DBCR_GC] <> 0
                )
            ),1
        )
    Can you check the sumx measure and let me know what is the issue please?
     
    Regards