Forum Discussion

Lucky71's avatar
Lucky71
Frequent Visitor
3 years ago

DAX Create Dynamic Virtual Table - Based on Slicer

Hi

 

I want to be able to create Virtual Table which is dependant on a slicer.
If Region slicer is...
being used:           Return the entire table
NOT being used:   Return a Summarized version of table (without the region field)

 

Entire table

CustAlias |Region    |Month          |Value

Cust 01|Region1| 30/06/2022| 1350
Cust 01|Region1| 30/06/2023| 1550
Cust 01|Region2| 30/06/2022| 1350
Cust 01|Region2| 30/06/2023| 1430
Cust 01|Region3| 30/06/2022| 1650
Cust 01|Region3| 30/06/2023| 1500

 

 

Summarized version of table (with region field removed)

AliasMonthValue
Cust 01| 30/06/2022| 4350
Cust 01| 30/06/2023| 4480

 

I tried using....
IF  ISFILTERED(DateTable[Region])
Do this... <Return entire table>
Else...      <Return Summazied table> [Without the Region Field]

But IF will only return a Scaler value, and not a table.

Other posts have said you cannot return a table, but you can create a virtual table to then calc a measure value, but I 've not been able to make it work yet! 😫

 

Desired Output:

Where Region slicer is being used:          

   Measure1Measure2
Region|30/06/2022|30/06/2023|ARR Delta|Analysis
Region1|1350|1550|200|Upsell
Region2|1350|1430|80|Upsell
Region3|1650|1500|-150|Downsell
Total|4350|4480|130|Upsell

 

Where Region slicer is NOT being used: 

   |Measure1|Measure2
Alias|30/06/2022|30/06/2023|ARR Delta|Analysis
Cust 01|4350|4480|130|Upsell

 

Measure values will change dependant on if they are calculated on the entire table OR the summarized table.

 

Any help would really be appreciated. 
Thx 😁

3 Replies