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.

MohamedFowzan1's avatar
MohamedFowzan1
Super User
1 year ago

How about using ALL function to consider the entire table. Sorry about going back and forth, this is because its a little difficult without the pbix to know if this would solve the issue.

TestFlagCount = 
VAR SelectedDate = SELECTEDVALUE('DateTable'[Date])
RETURN
IF(
    ISBLANK(SelectedDate),
    0,
    CALCULATE(
        DISTINCTCOUNT('VW_CONS_PC_AR_LOAD'[pstng_date]),  
        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'[ac_doc_status] = "C" && 'VW_CONS_PC_AR_LOAD'[pstng_date] <= SelectedDate && 'VW_CONS_PC_AR_LOAD'[clear_date] >= SelectedDate)
            )
        )
    )
)


Or CountROWS

Test Flag =
VAR SelectedDate = SELECTEDVALUE('DateTable'[Date])
RETURN
IF(
    ISBLANK(SelectedDate),
    BLANK(),
    CALCULATE(
        COUNTROWS(
            FILTER(
                'VW_CONS_PC_AR_LOAD', --Incase none are working try adding ALL here as well
                (
                    '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
                )
            )
        )
    )
)


Would also be helpful to know what error or issue is faced when trying these and any further input on the behavior

vivek_babu's avatar
vivek_babu
Helper II
1 year ago

Hi MohamedFowzan1 

 

Thanks for the respone

 

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