Forum Discussion

vivek_babu's avatar
vivek_babu
Helper 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.

vivek_babu's avatar
vivek_babu
Helper II
1 year ago

Hi johnt75 

 

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


johnt75's avatar
johnt75
Super User
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.

  • vivek_babu's avatar
    vivek_babu
    Helper II
    1 year ago

    Hi johnt75 

     

    This measure worked,

     

    Test Sumx =
    VAR SelectedDate = [Selected_Date]
    RETURN
        IF (
            NOT ISBLANK(SelectedDate),
            SUMX(
                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
                    )
                    ),
                1
            ),
            BLANK()
        )
     
    Thanks for your support