Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
abhirajkakani
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


@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
sumit4732
Advocate II
Advocate II

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 

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 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 )
kaushikd
Resolver II
Resolver II

Capture.PNG

 

 

 

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.

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'

Yup now i got your question.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.