Forum Discussion

KmB's avatar
KmB
Regular Visitor
1 year ago
Solved

Can a DAX Calculate function handle a dynamic variable in the Filter step?

I have a database that was built to monitor status of process activities passing through several steps. The database has 'Workflow #' events with both dates and names recorded for each step as the work progresses.    In this example I called it ‘RawData’.

 

I have a list of names that we could / should see in the RawData.   I called this table ‘NameTbl’. Every column in the Raw Data that potentially holds a name has a relationship built to the NameTbl although all relationships are inactive unless in use.

 

I have [of course] a calendar table that helps me build display visuals for several pages in the larger report.  Every Date field / column in the RawData has a relationship built to the basic ‘Calendar’ table.  Currently only one relationship is active, but we can use any of them as needed.

 

I use a DAX formula to get the counts  ->   Step1count = CALCULATE(COUNT('RawData'[Step1 Date]),FILTER( 'RawData', AND('RawData'[Step1 User]= "Sarah Morrison", 'RawData'[Step1 Date] ='Calendar'[Date] ) ))+0  

The “+0” at the end ensures that I do not have a Blank() where I expect to see a number.

Please note that to get the AND statement to work I had to type in the actual name text as is found in the RawData.  I Really need that to be a dynamic variable…

 

I have used a measure to capture the selected name  ->  SelectedName = SELECTEDVALUE ( 'NamesTbl'[All Names], "Oops" ) 

Again – the “Oops” is there so that I don’t end up with a BLANK() when I expect a value.

 

Substituting the 'SelectedName' measure into the spot where I typed in the actual name text does not work…   I susspect because the value “Oops” is not found in the list of names… and until a selection is actually made the DAX can not work.

I have exhausted all my tricks and alternat ideas.  Do any of you know how I can take a ‘selected’ measure result and use it as part of a two-level filter in a Calculation?  Am I missing some simple cross-tab syntax trick  that makes all this easy?

Need Help
  • lbendlin's avatar
    lbendlin
    1 year ago

    You will want to use TREATAS for this scenario. That allows you to transfer filters across tables even if they are unrelated.

  • v-veshwara-msft's avatar
    v-veshwara-msft
    1 year ago

    Hi KmB ,

    Thanks for using Microsoft Fabric Community,

    Just wanted to check in to see if this query has been resolved. If not, please consider the following approach which might help with the dynamic filtering issue you described.

     

    As suggested by lbendlin , using the TREATAS function is a great way to dynamically apply filters across tables - especially when working with disconnected tables or inactive relationships.

    Here’s how you can revise your measure:

    Step1Count =
CALCULATE (
    COUNT ( 'RawData'[Step1 Date] ),
    TREATAS ( { [SelectedName] }, 'RawData'[Step1 User] ),
    'RawData'[Step1 Date] = 'Calendar'[Date]
) + 0

     

    This allows the value returned by [SelectedName] to be projected as a filter onto the RawData[Step1 User] column, even when relationships are inactive or not defined.

    You can read more about how TREATAS works in the official documentation:
    TREATAS function - DAX | Microsoft Learn

     

    Hope this helps. Please reach out for further assistance.
    If this post helps, then please consider to Accept as the solution to help the other members find it more quickly and a kudos would be appreciated.

     

    Thank you.

     

4 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    1 year ago

    You will want to use TREATAS for this scenario. That allows you to transfer filters across tables even if they are unrelated.

  • v-veshwara-msft's avatar
    v-veshwara-msft
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi KmB ,

    Thanks for using Microsoft Fabric Community,

    Just wanted to check in to see if this query has been resolved. If not, please consider the following approach which might help with the dynamic filtering issue you described.

     

    As suggested by lbendlin , using the TREATAS function is a great way to dynamically apply filters across tables - especially when working with disconnected tables or inactive relationships.

    Here’s how you can revise your measure:

    Step1Count =
CALCULATE (
    COUNT ( 'RawData'[Step1 Date] ),
    TREATAS ( { [SelectedName] }, 'RawData'[Step1 User] ),
    'RawData'[Step1 Date] = 'Calendar'[Date]
) + 0

     

    This allows the value returned by [SelectedName] to be projected as a filter onto the RawData[Step1 User] column, even when relationships are inactive or not defined.

    You can read more about how TREATAS works in the official documentation:
    TREATAS function - DAX | Microsoft Learn

     

    Hope this helps. Please reach out for further assistance.
    If this post helps, then please consider to Accept as the solution to help the other members find it more quickly and a kudos would be appreciated.

     

    Thank you.

     

  • v-veshwara-msft's avatar
    v-veshwara-msft
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi KmB ,

    Just checking in to see if you query is resolved and if any responses were helpful. If so, kindly consider marking the helpful reply as 'Accepted Solution' to help others with similar queries. 

    Otherwise, feel free to reach out for further assistance.

    Thank you.

  • v-veshwara-msft's avatar
    v-veshwara-msft
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi KmB ,

    Following up to see if your query has been resolved. If any of the responses helped, please consider marking the helpful reply as the 'Accepted Solution' to assist others with similar questions.

    If you're still facing issues, feel free to reach out.

    Thank you.