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

Create Calculated table based on slicer selection

I've a table with ID, Names and Day column as under :

IDNameDays
1A30 days or less
1A30 days or less
1A30 days or less
1A31 to 60 days
2B30 days or less
2B31 to 60 days
2B61 to 90 days
2B61 to 90 days
3C90+ days
3C61 to 90 days

 

Alongside I've two slicers, coming from two different tables with such one column each 

 

slicer.PNG




What I'm trying to do here is, based on the user slicer selection, I want the table to be filtered and only output "Name" that arent present in the first slicer selection, also the anti-join needs to be done on the ID column

So for the above slicer selection, viz. 30 days or less compare with 31 to 60 days, the results should be a table with column "Name" and just record "C" as its the only Name thats not present in the "30 days or less"  but present in "31 to 60 days"




1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II
Super User II

@dollarvora 

Is this what you are looking for? 
Result.JPG

 If so, the model is set up like this:

One benefit of using this method is that you can multi-select in the slicers.

Model.JPG

 

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

4 REPLIES 4
Highlighted
Super User IV
Super User IV

@dollarvora ,

Create a measure like this plot againt name

if(countx(Tables[Days])+0 =0, 1, blank())

 

or

Table like

except(all(Table[name]), allselected(Table[Name])

 

and measure like

countrows(except(all(Table[name]), allselected(Table[Name]) )



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted

Hi @amitchandak ,

I seem to be getting blank values, alos
I'm not concerned about the counts, I'm more interested in getting the names that are present on the table fitered from second slicer but not present in the table filtered from the first slicer

Highlighted

@dollarvora ,Can you share a sample pbix after removing sensitive data.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Super User II
Super User II

@dollarvora 

Is this what you are looking for? 
Result.JPG

 If so, the model is set up like this:

One benefit of using this method is that you can multi-select in the slicers.

Model.JPG

 

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors