Dynamic measure calculation using selected date from the slicer

[vivek_babu]

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 date table. When user selects the date from the slicer i need to use that date and create a flag measure. I cannot use calculated column to filter the rows as the date comes dynamically based on user selection. I created below flag measure but this giving me incorrect results. I am testing the measure with 12/01/2022 as the user selection date. If i use the same logic in the backend snowflake table as where clause i am getting 66841 rows but if use the same logic as measure in power bi i am getting way too many rows. Please check the below measure logic,

[User_Selected_Date] = 12/01/2022 (This comes from the slicer selection)

Test Flag =

VAR SelectedDate = [User_Selected_Date]

VAR DocStatus = MAX(VW_CONS_PC_AR_LOAD[ac_doc_status])

VAR PostingDate = MAX(VW_CONS_PC_AR_LOAD[pstng_date])

VAR ClearDate = MAX(VW_CONS_PC_AR_LOAD[clear_date])

RETURN

IF (

   NOT ISBLANK(SelectedDate) &&

   (

       (DocStatus = "0" && PostingDate <= SelectedDate) ||

       (DocStatus = "C" && PostingDate <= SelectedDate && ClearDate >= SelectedDate)

   ),

   1,

   0

)
This measure should return PostingDate values <= 12/01/2022 but i am getting dates till 7/1/2025 which is wrong so not sure what is the issue 

Please someone help me to fix this logic

Regards

@FBergamaschi @OwenAuger @burakkaragoz @rohit1991 @lbendlin @andrewsommer @AmiraBedh @jaineshp @danextian @johnt75 @Greg_Deckler @kushanNa @Ritaf1983 @MasonMA @MFelix @amitchandak 

[v-nmadadi-msft]

Hi @vivek_babu 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

[vivek_babu]

Hi @v-nmadadi-msft 

Yes i have accepeted the solution 

Thank You

[johnt75]

Use Performance Analyzer to get the query for the visual and run it in DAX Query View, or DAX Studio. That will show you where the Flag measure is being evaluated. Sometimes it can get tricky when you use a measure as a filter when the measure is not in the visual itself.

Also, does the visual contain a column which would uniquely identify a row in your data, or is the data being grouped? If the data is being grouped then MAX will return the max value for the entire group, not an individual row.

[vivek_babu]

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 · Accepted solution]

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]

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 

[vivek_babu]

Hi @johnt75 

Thanks for the response

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

[wardy912]

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]

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

[vivek_babu]

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]

Hi @MohamedFowzan1 

Thanks for the quick response,

I cannot directly access the table columns inside the measure. I am getting below error,

 A single value for column 'ac_doc_status' in table 'VW_CONS_PC_AR_LOAD' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Because of this reason i used MAX aggregate function in the measure but that is not working properly 

Regards

[MohamedFowzan1]

Got it

SUMX would make this iterative. 

For Measure try to use SUMX to make the formula iterative by row. If this doesnt help as well, it would be easier if sample data is provided.

I believe in filter context, you wouldn't have to use MAX, let me know if you get any error for this.

Test Flag Measure =
VAR SelectedDate = SELECTEDVALUE('DateTable'[Date])
RETURN
IF(
    ISBLANK(SelectedDate),
    BLANK(),
    SUMX(
        FILTER(
            'VW_CONS_PC_AR_LOAD',
            (
                ('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)
            )
        ),
        1
    )
)

[vivek_babu]

Hi @MohamedFowzan1 

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

[MohamedFowzan1]

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]

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

[MohamedFowzan1]

Hi @vivek_babu 
Could you check if this works, the MAX might be getting one particular value from the column without proper row context. 

Test Flag =
VAR SelectedDate = SELECTEDVALUE('DateTable'[Date]) --Basically trying to get the date the user has selected from slicer selection (Use the field used in the slicer here)
RETURN
IF(
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)
),
1,
0
)

Also give a try using selectedvalue in place of MAX just incase its appearing appropriately in each row.

Please provide further context if this doesn't help