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.

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

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's avatar
    MohamedFowzan1
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      vivek_babu
      Icon for Helper II rankHelper II
      1 year ago

      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's avatar
        MohamedFowzan1
        Icon for Super User rankSuper 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