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.

johnt75's avatar
johnt75
Super User
1 year ago

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'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