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.

wardy912's avatar
wardy912
Super 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
Helper 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