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

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
Super User
Super User

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!  

4 REPLIES 4
Super User
Super User

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!  

gluizqueiroz Regular Visitor
Regular Visitor

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)

Super User
Super User

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!  

gluizqueiroz Regular Visitor
Regular Visitor

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!