Forum Discussion

daniq's avatar
daniq
Frequent Visitor
1 year ago
Solved

Apperantly identical queries, different results

I have these two measures:

 
$ Costo No Filter = CALCULATE(SUM(EjecPtal[Valor COP])*-1,USERELATIONSHIP(Dates[Date], EjecPtal[Date]))
$ Costo Comercial = CALCULATE(SUM(EjecPtal[Valor COP])*-1, FILTER(EjecPtal, EjecPtal[CLIENTE] = "COMERCIAL"), USERELATIONSHIP(Dates[Date], EjecPtal[Date]))
 
I can't understand why the second one would only return a total correctly, but it won't work at row level:
 

 

 

 
For the record, I could also filter by other column than client, but I it still won't work as I expect. For example:
 
This works as expected:
$ Costo Externo = CALCULATE(SUM(EjecPtal[Valor COP])*-1, USERELATIONSHIP(Dates[Date], EjecPtal[Date]), FILTER(EjecPtal, EjecPtal[SUBORIGEN] = "COSTO EXTERNO"))
This still lacks row level detail, only works as a total:
$ Costo Comercial 2 = CALCULATE(SUM(EjecPtal[Valor COP])*-1, USERELATIONSHIP(Dates[Date], EjecPtal[Date]), FILTER(EjecPtal, EjecPtal[SUBORIGEN] = "Costo nómina comercial"))
 

 

 

  • daniq's avatar
    daniq
    1 year ago

    This won't work, buuuut it leaded me to play with chatgpt and find out the solution!

     

    Basically CALCULATE can take a filter as a parameter:

     

    $ Costo Comercial = CALCULATE(
        SUM(EjecPtal[Valor COP]) * -1, 
        USERELATIONSHIP(Dates[Date], EjecPtal[Date]), 
        EjecPtal[CLIENTE] = "COMERCIAL"
    )

     

    A final note would be that DAX Query View will write EXACTLY the same for both:

     

     $ Costo Comercial = CALCULATE( SUM(EjecPtal[Valor COP]) * -1,  USERELATIONSHIP(Dates[Date], EjecPtal[Date]),  EjecPtal[CLIENTE] = "COMERCIAL")
    $ Costo Comercial 2 = CALCULATE(SUM(EjecPtal[Valor COP])*-1, FILTER(EjecPtal, EjecPtal[CLIENTE] = "COMERCIAL"), USERELATIONSHIP(Dates[Date], EjecPtal[Date]))

     

    DEFINE
        VAR __DS0FilterTable = 
            TREATAS({"COMERCIAL"}, 'EjecPtal'[CLIENTE])
        VAR __DS0FilterTable2 = 
            TREATAS(
                {"Ene. 2024",
                    "Feb. 2024",
                    "Mar. 2024",
                    "Abr. 2024",
                    "May. 2024",
                    "Jun. 2024",
                    "Jul. 2024"},
                'Dates'[Month & Year]
            )
        VAR __DS0FilterTable3 = 
            FILTER(
                KEEPFILTERS(VALUES('Project'[Name])),
                NOT('Project'[Name] IN {"Base Project Template",
                    "Project 1"})
            )
    EVALUATE
        SUMMARIZECOLUMNS(
            __DS0FilterTable,
            __DS0FilterTable2,
            __DS0FilterTable3,
            "v__Costo_Comercial", IGNORE('EjecPtal'[$ Costo Comercial]) // here it would change the names to Costo Comercial 2
        )

     

    And the table will call both with the same context:

     

     

     

    DEFINE
        VAR __DS0FilterTable = 
            TREATAS({"COMERCIAL"}, 'EjecPtal'[CLIENTE])
        VAR __DS0FilterTable2 = 
            TREATAS(
                {"Ene. 2024",
                    "Feb. 2024",
                    "Mar. 2024",
                    "Abr. 2024",
                    "May. 2024",
                    "Jun. 2024",
                    "Jul. 2024"},
                'Dates'[Month & Year]
            )
        VAR __DS0FilterTable3 = 
            FILTER(
                KEEPFILTERS(VALUES('Project'[Name])),
                NOT('Project'[Name] IN {"Base Project Template",
                    "Project 1"})
            )
        VAR __DS0Core = 
            SUMMARIZECOLUMNS(
                ROLLUPADDISSUBTOTAL(
                    ROLLUPGROUP('Dates'[Month & Year], 'Dates'[Month], 'EjecPtal'[CLIENTE], 'Dates'[Date]), "IsGrandTotalRowTotal"
                ),
                __DS0FilterTable,
                __DS0FilterTable2,
                __DS0FilterTable3,
                "v__Costo_Comercial_2", 'EjecPtal'[$ Costo Comercial 2], 
                "v__Costo_Comercial", 'EjecPtal'[$ Costo Comercial]
            )
        VAR __DS0PrimaryWindowed = 
            TOPN(
                502,
                __DS0Core,
                [IsGrandTotalRowTotal],
                0,
                'Dates'[Month],
                1,
                'Dates'[Month & Year],
                1,
                'EjecPtal'[CLIENTE],
                1,
                'Dates'[Date],
                1
            )
    EVALUATE
        __DS0PrimaryWindowed
    ORDER BY
        [IsGrandTotalRowTotal] DESC,
        'Dates'[Month],
        'Dates'[Month & Year],
        'EjecPtal'[CLIENTE],
        'Dates'[Date]

     

    Really ood behaviour, I obviously must be unseeing something. Even GPT agrees that using FILTER would work better at row level rather that filtering as a parameter to CALCULATE.

     

4 Replies

  • AnkitKukreja's avatar
    AnkitKukreja
    Super User
    1 year ago

    Hi! daniq 

     

    This could be due to some gap in data model relationship or some filters flowing through, if you can share the sample data with desired solution then I can have a look at it. Also, make sure you are not sharing in sensitive data.

     

     

    • daniq's avatar
      daniq
      Frequent Visitor
      1 year ago

      HI, I've just found out the solution and posted it to FLiflop1 answer. It was indeed a filtering issue, just can't understand why exactly.

      Thanks!

  • FlipFlop1's avatar
    FlipFlop1
    Advocate I
    1 year ago
    Does this will show at row level, but not at total?
    $ Costo Comercial = 
    VAR one = CALCULATE(SUM(EjecPtal[Valor COP])*-1, USERELATIONSHIP(Dates[Date], EjecPtal[Date])
    RETURN
    IF(EjecPtal[CLIENTE] = "COMERCIAL",  one, BLANK())
    • daniq's avatar
      daniq
      Frequent Visitor
      1 year ago

      This won't work, buuuut it leaded me to play with chatgpt and find out the solution!

       

      Basically CALCULATE can take a filter as a parameter:

       

      $ Costo Comercial = CALCULATE(
          SUM(EjecPtal[Valor COP]) * -1, 
          USERELATIONSHIP(Dates[Date], EjecPtal[Date]), 
          EjecPtal[CLIENTE] = "COMERCIAL"
      )

       

      A final note would be that DAX Query View will write EXACTLY the same for both:

       

       $ Costo Comercial = CALCULATE( SUM(EjecPtal[Valor COP]) * -1,  USERELATIONSHIP(Dates[Date], EjecPtal[Date]),  EjecPtal[CLIENTE] = "COMERCIAL")
      $ Costo Comercial 2 = CALCULATE(SUM(EjecPtal[Valor COP])*-1, FILTER(EjecPtal, EjecPtal[CLIENTE] = "COMERCIAL"), USERELATIONSHIP(Dates[Date], EjecPtal[Date]))

       

      DEFINE
          VAR __DS0FilterTable = 
              TREATAS({"COMERCIAL"}, 'EjecPtal'[CLIENTE])
          VAR __DS0FilterTable2 = 
              TREATAS(
                  {"Ene. 2024",
                      "Feb. 2024",
                      "Mar. 2024",
                      "Abr. 2024",
                      "May. 2024",
                      "Jun. 2024",
                      "Jul. 2024"},
                  'Dates'[Month & Year]
              )
          VAR __DS0FilterTable3 = 
              FILTER(
                  KEEPFILTERS(VALUES('Project'[Name])),
                  NOT('Project'[Name] IN {"Base Project Template",
                      "Project 1"})
              )
      EVALUATE
          SUMMARIZECOLUMNS(
              __DS0FilterTable,
              __DS0FilterTable2,
              __DS0FilterTable3,
              "v__Costo_Comercial", IGNORE('EjecPtal'[$ Costo Comercial]) // here it would change the names to Costo Comercial 2
          )

       

      And the table will call both with the same context:

       

       

       

      DEFINE
          VAR __DS0FilterTable = 
              TREATAS({"COMERCIAL"}, 'EjecPtal'[CLIENTE])
          VAR __DS0FilterTable2 = 
              TREATAS(
                  {"Ene. 2024",
                      "Feb. 2024",
                      "Mar. 2024",
                      "Abr. 2024",
                      "May. 2024",
                      "Jun. 2024",
                      "Jul. 2024"},
                  'Dates'[Month & Year]
              )
          VAR __DS0FilterTable3 = 
              FILTER(
                  KEEPFILTERS(VALUES('Project'[Name])),
                  NOT('Project'[Name] IN {"Base Project Template",
                      "Project 1"})
              )
          VAR __DS0Core = 
              SUMMARIZECOLUMNS(
                  ROLLUPADDISSUBTOTAL(
                      ROLLUPGROUP('Dates'[Month & Year], 'Dates'[Month], 'EjecPtal'[CLIENTE], 'Dates'[Date]), "IsGrandTotalRowTotal"
                  ),
                  __DS0FilterTable,
                  __DS0FilterTable2,
                  __DS0FilterTable3,
                  "v__Costo_Comercial_2", 'EjecPtal'[$ Costo Comercial 2], 
                  "v__Costo_Comercial", 'EjecPtal'[$ Costo Comercial]
              )
          VAR __DS0PrimaryWindowed = 
              TOPN(
                  502,
                  __DS0Core,
                  [IsGrandTotalRowTotal],
                  0,
                  'Dates'[Month],
                  1,
                  'Dates'[Month & Year],
                  1,
                  'EjecPtal'[CLIENTE],
                  1,
                  'Dates'[Date],
                  1
              )
      EVALUATE
          __DS0PrimaryWindowed
      ORDER BY
          [IsGrandTotalRowTotal] DESC,
          'Dates'[Month],
          'Dates'[Month & Year],
          'EjecPtal'[CLIENTE],
          'Dates'[Date]

       

      Really ood behaviour, I obviously must be unseeing something. Even GPT agrees that using FILTER would work better at row level rather that filtering as a parameter to CALCULATE.