Forum Discussion
Dashboard assistance double usage
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.
Super UserTo 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.
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.