Forum Discussion

AxiomaticTwo's avatar
AxiomaticTwo
Frequent Visitor
2 years ago
Solved

Debugging DAX Calculations (teach to fish)

I'm in some complicated stuff and I can't visuzualize what is going on under the hood of DAX.  Can you answer or point me to a blog, video, etc. that will allow me to help myself see that what I assume is actually happening. This is regarding shadow filters, context transition, initial filter context, and revised filter context.

I know I should be able to use a combination of EVALUATE and DEFINE and I have for simple things, but when I'm trying to recreate a table in a Power BI report in Tabular Editor with a DAX Query I'm finding it difficult. 

TL;DR when I write the following DAX code how can I pick apart each section to visualize it using DAX Studio or Tabular Editor or Calculated Tables in PBIX even.

var _filter = 
    FILTER(
        ALLSELECTED ( 'Month'[Fiscal Year ID] ),
        'Month' [Fiscal Year ID] = MAX( 'Month'[Fiscal Year ID] ))

VAR _OpenStores =
        CALCULATETABLE (
            FILTER (
                ALLSELECTED ( 'tblQualifies'[StoreID] ),      
                CALCULATE ( SELECTEDVALUE ( 'tblQualifies'[Qualifies]) 
                )  
                = 1                             
            )
            ,_filter

 
Thanks in advance if you are able to help.

  • AxiomaticTwo's avatar
    AxiomaticTwo
    2 years ago

    Thanks but that is part of the problem. The blogs do a good enough job of explaining the functions but not of showing how to validate functions are working as expected.

    I think I developed some stuff in the last couple of hours that has helped but would love to hear anyone elses tricks patterns they use.

    How to see values of a single column table output, within the context of a dimension?
     - Create table visual, drag a dimension column in first.
     - Create a new measure where you copy paste your table expression.

     - Wrap that table expression in concatentatex.

     - I want to evaluate these results of var _filter so I'll write measure [test_filter]

    --here is the table variable from a much longer measure to evalute in a specific context:
var _filter = 
       FILTER(
            ALLSELECTED(
                Month[Fiscal Month Num Of Year]
            ),
            Month[Fiscal Month Num Of Year]
            <= selectedvalue( Month[Fiscal Month Num Of Year], 12 )
        )

--we write a new measure that looks like the following with concatenatex wrapping:
test5 filterv2 = 
    CONCATENATEX(
        FILTER(
            ALLSELECTED(
                Month[Fiscal Month Num Of Year]
            ),
            Month[Fiscal Month Num Of Year]
            <= selectedvalue( Month[Fiscal Month Num Of Year], 12 )
        ),
        'Month'[Fiscal Month Num Of Year],
        "-")

     

    The result looks like this:

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi AxiomaticTwo ,

     

    I found a couple of resources that might be helpful:

    Understanding Context Transition - SQLBI

    The definitive guide to ALLSELECTED - SQLBI

    Context Transition and Filters in CALCULATE - SQLBI

    Introduction to Filter Context in Power BI (enterprisedna.co)

    Power BI Filters: Invoking Context Transitions (enterprisedna.co)

     

    Debugging Strategy: Break down your DAX expressions into smaller parts and test each part individually. This will help you isolate and understand the behavior of each component of your DAX formula.

     

    Best Regards,

    Neeko Tang

    If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

  • AxiomaticTwo's avatar
    AxiomaticTwo
    Frequent Visitor
    2 years ago

    Thanks but that is part of the problem. The blogs do a good enough job of explaining the functions but not of showing how to validate functions are working as expected.

    I think I developed some stuff in the last couple of hours that has helped but would love to hear anyone elses tricks patterns they use.

    How to see values of a single column table output, within the context of a dimension?
     - Create table visual, drag a dimension column in first.
     - Create a new measure where you copy paste your table expression.

     - Wrap that table expression in concatentatex.

     - I want to evaluate these results of var _filter so I'll write measure [test_filter]

    --here is the table variable from a much longer measure to evalute in a specific context:
var _filter = 
       FILTER(
            ALLSELECTED(
                Month[Fiscal Month Num Of Year]
            ),
            Month[Fiscal Month Num Of Year]
            <= selectedvalue( Month[Fiscal Month Num Of Year], 12 )
        )

--we write a new measure that looks like the following with concatenatex wrapping:
test5 filterv2 = 
    CONCATENATEX(
        FILTER(
            ALLSELECTED(
                Month[Fiscal Month Num Of Year]
            ),
            Month[Fiscal Month Num Of Year]
            <= selectedvalue( Month[Fiscal Month Num Of Year], 12 )
        ),
        'Month'[Fiscal Month Num Of Year],
        "-")

     

    The result looks like this: