cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

How to create a dynamic table that always contains a especific rows even if is filtered on Power BI

I have the following table:

I have a filter by City on my page.

When I filter "Rome" for example, I need that table returns the following lines:

 

Note the cases with Type = P is affected by filter but all lines with Type = T is not affected by filter.

In other words, I need to create a table that always returns the lines with Type = T even if is filtered, but Type = P suffer the action of filter.

I tried the following DAX code:

 

CalcTable = 
UNION
(
CALCULATETABLE(VALUES(Table1[Cod]); Table1[Type] = "P");
CALCULATETABLE(VALUES(Table1[Cod]); Table1[Ind_Tipo] = "T"; ALL(Table1))
)

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
LivioLanzo Super Contributor
Super Contributor

Re: How to create a dynamic table that always contains a especific rows even if is filtered on Power

Hello @gluizqueiroz

 

You can't have dynamic tables in Power BI but you can do it like this:

 

Create data model like this, where cities contains the list of unique cities, this is what you will use in your slicer:

 

Capture.PNG

 

Then, add this measure:

 

Measure = 
CALCULATE(
    COUNTROWS( Data ),
    UNION(
        CROSSJOIN( TREATAS( {"P"}, Data[Type] ), TREATAS( VALUES( Cities[City] ), Data[City]  ) ),
        CROSSJOIN( TREATAS( {"T"}, Data[Type] ), TREATAS( ALL( Cities[City] ), Data[City] ) )
    )
)

Then, you can add a Table Visual, where you would add the columns of your 'Data' Table. Make sure each element is set to 'Do Not Summarize'. Instead of displaying the measure as an additional column as I did below, you could add it to the Visual Filters section with a condition that it has to be equal to 1:

 

Capture.PNG

Capture.PNG

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

View solution in original post

4 REPLIES 4
LivioLanzo Super Contributor
Super Contributor

Re: How to create a dynamic table that always contains a especific rows even if is filtered on Power

Hello @gluizqueiroz

 

You can't have dynamic tables in Power BI but you can do it like this:

 

Create data model like this, where cities contains the list of unique cities, this is what you will use in your slicer:

 

Capture.PNG

 

Then, add this measure:

 

Measure = 
CALCULATE(
    COUNTROWS( Data ),
    UNION(
        CROSSJOIN( TREATAS( {"P"}, Data[Type] ), TREATAS( VALUES( Cities[City] ), Data[City]  ) ),
        CROSSJOIN( TREATAS( {"T"}, Data[Type] ), TREATAS( ALL( Cities[City] ), Data[City] ) )
    )
)

Then, you can add a Table Visual, where you would add the columns of your 'Data' Table. Make sure each element is set to 'Do Not Summarize'. Instead of displaying the measure as an additional column as I did below, you could add it to the Visual Filters section with a condition that it has to be equal to 1:

 

Capture.PNG

Capture.PNG

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

View solution in original post

Re: How to create a dynamic table that always contains a especific rows even if is filtered on Power

Hey @LivioLanzo!
It works perfect for my visual table, but I have a map too, can I use this technique for a map? 
My visual table is showing the correct cases, its perfect, but for my map is not working, did you know why?

I added Latitude and Longitude, and set the colors for "Type", if "Type = T" show the green balls, if the "Type = P" show red balls, green balls always should appear, like on visual table. 
Can you help me on my map too? The structure is the same, this is in the same page (Visual Table and Map and Filters)

LivioLanzo Super Contributor
Super Contributor

Re: How to create a dynamic table that always contains a especific rows even if is filtered on Power

Hello@gluizqueiroz

 

try using the same measure as a filter for the visual:

 

Capture.PNG

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Highlighted

Re: How to create a dynamic table that always contains a especific rows even if is filtered on Power

Hey @LivioLanzo!
It works fine fine fine!

Thanks so much for help and tech me some new things!
You are awesome man, have a nice day!
Again, thanks so much!

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 52 members 1,129 guests
Please welcome our newest community members: