Forum Discussion
Dashboard assistance double usage
I have a dashboard driven off an excel file and i have to manual go through a user to see if they have a licensed product assigned to them via a cloud or a subscription. Is there a way that a query or an IF command is implemented to show where the user has been using BOTH. So if i clicked on a filter(button) where it highlighted the products or users who had both Cloud and Sub allocations. Any help would be great.
To identify users who have had both "Cloud" and "Sub" license types assigned to them within the same period in Power BI, you can use DAX to create a calculated column or measure. Here’s a step-by-step guide to help you achieve this:
Step 1: Create a Calculated Column to Check for Both Types
1. Add a Calculated Column in your table to identify users with both "Cloud" and "Sub" types within the same period.
DAX HasBoth = IF( CALCULATE( COUNTROWS('Table'), FILTER( 'Table', 'Table'[Email] = EARLIER('Table'[Email]) && 'Table'[Date] = EARLIER('Table'[Date]) && 'Table'[Type] = "Cloud" ) ) > 0 && CALCULATE( COUNTROWS('Table'), FILTER( 'Table', 'Table'[Email] = EARLIER('Table'[Email]) && 'Table'[Date] = EARLIER('Table'[Date]) && 'Table'[Type] = "Sub" ) ) > 0, "Both", "Single" )This column will return "Both" if a user has both "Cloud" and "Sub" types assigned on the same date, and "Single" otherwise.
Step 2: Create a Filter or Conditional Formatting in Power BI
1. Conditional Formatting:
- Go to the table visual in your Power BI report.
- Apply conditional formatting to the `HasBoth` column to highlight rows with the value "Both".
2. Filter by "Both":
- Use the `HasBoth` column in a slicer or filter pane to display only rows where `HasBoth` = "Both".Step 3: Visualize in a Timeline (Optional)
If you want to visualize this on a timeline to see when users held both licenses, you can create a line or bar chart using:
- Date as the X-axis
- Count of users with "Both" on the Y-axisThis approach should give you a clear indication of users who held both types of licenses within a given period.
6 Replies
- vojtechsima
Super User
depends how much you wanna over engineer it or not, if you just wanna have table visual and see only those with two ore more, you can write measure like this:
Measure = CALCULATE( DISTINCTCOUNT('Table'[Type]), ALLEXCEPT('Table', 'Table'[Email], 'Table'[Product]) )and then you can sort it by descending value or filter on visual to display only 2+.
I would like to have a button to shortlist all the users who have had both Cloud and Subs assignment. And not having to manually go through the user list manually.
- FarhanJeelani
To highlight users with both Cloud and Subscription allocations in your Power BI dashboard, you can create a calculated column or measure to identify these users:
1. Add a Calculated Column:
DAX
HasBoth =
IF(
COUNTROWS(FILTER(Table, Table[User] = EARLIER(Table[User]) && Table[ProductType] = "Cloud")) > 0 &&
COUNTROWS(FILTER(Table, Table[User] = EARLIER(Table[User]) && Table[ProductType] = "Subscription")) > 0,
"Both",
"Single"
)2. Use a Filter/Button in the Report:
- Create a slicer or button to filter only users with `HasBoth = "Both"`.
- This will highlight only those users with both types of allocations.This setup allows quick filtering for users with both Cloud and Subscription products.
- FarhanJeelani
Please mark it as solution if this solved your issue
Its only giving a single entry in the column. I would like it that it will highlight (Identify) the users who have had both Cloud and Sub licenses assigned to them at a certain period. The product doesnt really matter at the minute.
- FarhanJeelani
To identify users who have had both "Cloud" and "Sub" license types assigned to them within the same period in Power BI, you can use DAX to create a calculated column or measure. Here’s a step-by-step guide to help you achieve this:
Step 1: Create a Calculated Column to Check for Both Types
1. Add a Calculated Column in your table to identify users with both "Cloud" and "Sub" types within the same period.
DAX HasBoth = IF( CALCULATE( COUNTROWS('Table'), FILTER( 'Table', 'Table'[Email] = EARLIER('Table'[Email]) && 'Table'[Date] = EARLIER('Table'[Date]) && 'Table'[Type] = "Cloud" ) ) > 0 && CALCULATE( COUNTROWS('Table'), FILTER( 'Table', 'Table'[Email] = EARLIER('Table'[Email]) && 'Table'[Date] = EARLIER('Table'[Date]) && 'Table'[Type] = "Sub" ) ) > 0, "Both", "Single" )This column will return "Both" if a user has both "Cloud" and "Sub" types assigned on the same date, and "Single" otherwise.
Step 2: Create a Filter or Conditional Formatting in Power BI
1. Conditional Formatting:
- Go to the table visual in your Power BI report.
- Apply conditional formatting to the `HasBoth` column to highlight rows with the value "Both".
2. Filter by "Both":
- Use the `HasBoth` column in a slicer or filter pane to display only rows where `HasBoth` = "Both".Step 3: Visualize in a Timeline (Optional)
If you want to visualize this on a timeline to see when users held both licenses, you can create a line or bar chart using:
- Date as the X-axis
- Count of users with "Both" on the Y-axisThis approach should give you a clear indication of users who held both types of licenses within a given period.
