Create a Calculated Table using a UNION that preserves all values in one table

[mcairney]

I have two tables withe the same structure.

In one I have rows that I always want to include.  In the other I have a slicer based on this which the user can select the rows they are interested in.

I want to then have a table which is the UNION of the two tables.

I have tried using :   CalcTable = UNION ( 'Filtered Tabled', ALL( 'Static Table))

I then put a Table on the report page that displays the values from the CalcTable.   However it does not seem to include the static rows and only shows me the filtered rows.

What am I doing wrong here?

[marej123]

This is awesome!! Thank you!! @LivioLanzo 

[v-piga-msft]

Hi @mcairney,

From your pbix, it seems that you have created the relationship like below. So when you  select the slicer based on Site Key from User Site table, it will show the filtered value.

I'm a little confused about your requirement, do you only want to get the all rows like below which won't be filtered  by the selection of the slicer.

If it is, you could delete the relationship between the table User Site and All Sites, but that slicer seems to be meaningless if you remove the relationship.

Best Regards,

Cherry

[mcairney]

Hi Cherry

The relationships were where I was also trying different options to find what works.

What I need is to have 2 sets of Sites and then combine them.  However only the rows from the "User Site" table can be filtered and the ones in the "Reference Site" table are all included.   The combined set should then filter the "Site Metrics" table and the final data set then used on the chart to show the "Reference Site" differently from the "User Site".

So as I select e.g. the value "Kof18" in the slicer, the final table should have the values "Par15", "Feb22" and "Kof18"

[LivioLanzo]

Hi @mcairney

are you able to post a sample dataset ?

[mcairney]

 Here is a basic sample of what I'm trying to do   

[LivioLanzo]

Hello @mcairney

thanks for the file. You need to create a calculate table with this DAX Query:

You can download the file: https://1drv.ms/u/s!AiiWkkwHZChHj0R4TRtHxsgRDnct

UnionTable = 
UNION(
    ADDCOLUMNS( ALLNOBLANKROW('Reference Site'), "SourceTable", "ReferenceSite" ),
    ADDCOLUMNS( ALLNOBLANKROW('User Site'), "SourceTable", "UserSite" )
)

Do not relate it to any table. In the slicer, use the Key from the 'Site Metrics' table. Then add a table visual and add this measure as a visual filter = 1

Measure = 
CALCULATE(
    COUNTROWS( UnionTable ),
    TREATAS(
        UNION(
            CROSSJOIN( ALL( 'Site Metrics'[Site Key] ), {"ReferenceSite"} ),
            CROSSJOIN( VALUES( 'Site Metrics'[Site Key] ), {"UserSite"} )
        ),
        UnionTable[Site Key], UnionTable[SourceTable]
    )
)

[mcairney]

@LivioLanzo I worked out where my version was going wrong.

However, now I need to look at extending this a bit further.   If I have more than 1 Metrics table, does this solution extend out to be able to appply the same filter across that?

Thanks

[LivioLanzo]

Hi @mcairney

it should be possible. What are you trying to do exactly?

[mcairney]

@LivioLanzo  Assume that there is 2 additional tabl;es with a similar structure to Site Metrics  - lets call them Site Metrics2 and Site Metrics 3

Again they have relationships to Site Key and Region.

I want this slicer to also filter the values in those other tables.   I can mock this up if you need?

[v-piga-msft]

Hi @mcairney,

For your requirement, I have made a test. You could create the relationship like below.

Then the slicer of  Site Key and Region will filter the table Site Metrics2 and Site Metrics 3.

Best Regards,

Cherry