cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Dynamic Filtering of derived DAX table based on the filters applied to the source table

Hello,

 

I'm struggling with a problem where i have a excel connected table something like this

 

NameDateScore
User101-Jan-1710
User201-Jan-1711
User301-Jan-1712
User401-Jan-1713
User501-Jan-1710
User102-Jan-172111
User102-Jan-17233
User104-Jan-171733
User202-Jan-171920

 

Now, i have derived a DAX table which provides me with the SUM of the 'Score', grouped by the 'Name'. Something like this

 

User14087
User510
User312
User21931
User413

 

I would like this DAX table to update if i apply a visual filter of DATE on the first table. i.e. if the first table is filtered to only include 1-Jan-2017 and 2-Jan-2017, then the derived DAX table should change to

 

User12354
User510
User212
User21931
User413

 

Is this possible?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Dynamic Filtering of derived DAX table based on the filters applied to the source table


@abhirajkakani wrote:

Hi Sumit,

 

Thanks for the reply. The real problem which i'm trying to solve cannot be solved using a measure. It requires calculated columns and hence it is very necessary for me to get a derived DAX table.

 

I cannot share the real problem for the obvious reasons but measures cannot be used for the operations that i need to perform on the table.

 

 


@abhirajkakani

Not like measures, calculate columns/tables are computed during database processing(e.g. data refresh) and then stored in the model, they do not response to user selections on the report.

 

Measure actually may work, I think. You could declare calculated table variable and calculate against the variable, the variable would repsonse to the slicers.

 

Measure =
VAR tempTable =
    CALCULATETABLE (
        SUMMARIZE ( 'table', 'table'[Name], "TOTAL", SUM ( 'table'[Score] ) )
    )
RETURN
    COUNTROWS ( tempTable )

View solution in original post

7 REPLIES 7
Highlighted
Resolver II
Resolver II

Re: Dynamic Filtering of derived DAX table based on the filters applied to the source table

Capture.PNG

 

 

 

Highlighted
Frequent Visitor

Re: Dynamic Filtering of derived DAX table based on the filters applied to the source table

Hello Kaushik,

 

Thanks for the reply. However, I need the date filter to have an effect on the derived table.

 

I just gave these tables for reference. My actual problem has a lot of calculations which require a Derived DAX table.

Highlighted
Frequent Visitor

Re: Dynamic Filtering of derived DAX table based on the filters applied to the source table

Let me explain my problem again.

 

I have a 'source table' and i'm summarizing that table using DAX queries to get the 'derived table'. I'm creating a lot of calculated columns on the 'derived table'.

 

Now, if i apply a filter on my 'source table' then what i want is that the 'derived table' should be re-calculated to just include the filtered 'source table'

Highlighted
Resolver II
Resolver II

Re: Dynamic Filtering of derived DAX table based on the filters applied to the source table

Yup now i got your question.

Just give me some time to think.....Man Tongue

Highlighted
Advocate II
Advocate II

Re: Dynamic Filtering of derived DAX table based on the filters applied to the source table

Hi @abhirajkakani,

 

I dont think you need a seperate table to get roll up numbers at user level, you can just create a measure using ALLEXCEPT and put date column as exception, this will result in user level rolled up numbers and all the filters of you table will work on the measure, which will give you required output.

 

Hope this helps

-Sumit 

Highlighted
Frequent Visitor

Re: Dynamic Filtering of derived DAX table based on the filters applied to the source table

Hi Sumit,

 

Thanks for the reply. The real problem which i'm trying to solve cannot be solved using a measure. It requires calculated columns and hence it is very necessary for me to get a derived DAX table.

 

I cannot share the real problem for the obvious reasons but measures cannot be used for the operations that i need to perform on the table.

 

 

Highlighted
Microsoft
Microsoft

Re: Dynamic Filtering of derived DAX table based on the filters applied to the source table


@abhirajkakani wrote:

Hi Sumit,

 

Thanks for the reply. The real problem which i'm trying to solve cannot be solved using a measure. It requires calculated columns and hence it is very necessary for me to get a derived DAX table.

 

I cannot share the real problem for the obvious reasons but measures cannot be used for the operations that i need to perform on the table.

 

 


@abhirajkakani

Not like measures, calculate columns/tables are computed during database processing(e.g. data refresh) and then stored in the model, they do not response to user selections on the report.

 

Measure actually may work, I think. You could declare calculated table variable and calculate against the variable, the variable would repsonse to the slicers.

 

Measure =
VAR tempTable =
    CALCULATETABLE (
        SUMMARIZE ( 'table', 'table'[Name], "TOTAL", SUM ( 'table'[Score] ) )
    )
RETURN
    COUNTROWS ( tempTable )

View solution in original post

Helpful resources

Announcements
Ignite

Microsoft Ignite

This will be a conference that you do not want to miss!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors