Forum Discussion
Create Calculated table based on slicer selection
- 5 years ago
Is this what you are looking for?
If so, the model is set up like this:
One benefit of using this method is that you can multi-select in the slicers.
To calculate the compliant names, you can use this in your measures or to filter a table using the "filters on this visual" in the filter pane (see first image):
Name selected (but not in exlcuded) = VAR _select = CALCULATETABLE(VALUES('Fact'[Name]), TREATAS(VALUES('Date range selection'[Range Selection]), 'Fact'[Days])) VAR _Exclude = CALCULATETABLE(VALUES('Fact'[Name]), TREATAS(VALUES('Date range Exclude'[Range to be exlcuded]), 'Fact'[Days])) RETURN COUNTROWS(EXCEPT(_select, _Exclude))If you wish to list the names in a card visual for example, or in a column in a table/matrix, use:
Names Identified = VAR calc = CALCULATETABLE(VALUES('Fact'[Name]), FILTER('Fact', [Name selected (but not in exlcuded)] = 1)) RETURN CONCATENATEX(calc, 'Fact'[Name], ", ")The slicers are set up so that once you make a selection in the "Range Selection" slicer, the corresponding ranges are hidden in the "Range to be excluded" slicer (since you cannot select and exclude the same range simultaneously)
I've attached the PBIX file for your reference
Is this what you are looking for?
If so, the model is set up like this:
One benefit of using this method is that you can multi-select in the slicers.
To calculate the compliant names, you can use this in your measures or to filter a table using the "filters on this visual" in the filter pane (see first image):
Name selected (but not in exlcuded) =
VAR _select = CALCULATETABLE(VALUES('Fact'[Name]), TREATAS(VALUES('Date range selection'[Range Selection]), 'Fact'[Days]))
VAR _Exclude = CALCULATETABLE(VALUES('Fact'[Name]), TREATAS(VALUES('Date range Exclude'[Range to be exlcuded]), 'Fact'[Days]))
RETURN
COUNTROWS(EXCEPT(_select, _Exclude))
If you wish to list the names in a card visual for example, or in a column in a table/matrix, use:
Names Identified =
VAR calc = CALCULATETABLE(VALUES('Fact'[Name]), FILTER('Fact', [Name selected (but not in exlcuded)] = 1))
RETURN
CONCATENATEX(calc, 'Fact'[Name], ", ")
The slicers are set up so that once you make a selection in the "Range Selection" slicer, the corresponding ranges are hidden in the "Range to be excluded" slicer (since you cannot select and exclude the same range simultaneously)
I've attached the PBIX file for your reference