Forum Discussion
Tableau to Power Bi Migration
I want to create a matrix visual and i have a slicer for customer type like regular customer, distributor, partner, second slicer for topn,
now when the customer is selected then in matrix it should show customer id and customer name and rank them by booking amount, if distributor selected then show distributor id and its name, if partner then its id and name two and rank them on booking amount basis, any suggestion how to achieve this beacause i need two columns in row headers with single selection and ranking of them
Hi Anonymous ,
Thank you for reaching out to Microsoft fabric community forum.I also want to thanks bhanu_gautam for valuable input the suggested approach involving dynamic measures for calculating Booking Amount, implementing Top N filtering, and generating rankings is correct.
However, I would like to have a clarification regarding the use of SELECTEDVALUE() within calculated columns:
To implement dynamic switching in the matrix visual based on slicer selection (e.g., Customer Type), consider the following approach:
- Create a unified table using the UNION function that consolidates all relevant customer types along with their IDs and Names.
- Use measures (rather than calculated columns) to implement dynamic logic for both display and filtering including the Top N logic.
This method ensures the matrix visual responds correctly to slicer selections and maintains optimal performance and flexibility.
Hope this helps. Please reach out for further assistance.
If this post helps, then please consider to Accept as the solution to help the other members find it more quickly and a kudos would be appreciated.
Thank you.
5 Replies
Anonymous Create a Slicer for Customer Type
Ensure you have a table with customer types.You can use a numeric range slicer or create a table for Top N values:
TopNValues = GENERATESERIES(1, 20, 1)
Create a measure to rank customers based on the booking amount:
DAX
SelectedCustomerType = SELECTEDVALUE(CustomerTypes[CustomerType])BookingAmount =
SWITCH(
TRUE(),
SelectedCustomerType = "Regular Customer", SUM(RegularCustomers[BookingAmount]),
SelectedCustomerType = "Distributor", SUM(Distributors[BookingAmount]),
SelectedCustomerType = "Partner", SUM(Partners[BookingAmount])
)Rank =
RANKX(
ALLSELECTED(
SWITCH(
TRUE(),
SelectedCustomerType = "Regular Customer", RegularCustomers,
SelectedCustomerType = "Distributor", Distributors,
SelectedCustomerType = "Partner", Partners
)
),
[BookingAmount],
,
DESC,
DENSE
)Add a matrix visual and use the following fields:
Use a dynamic approach to switch between customer types. You can create a calculated column or use a measure to display the appropriate IDs and names.
DAX
CustomerID =
SWITCH(
TRUE(),
SelectedCustomerType = "Regular Customer", RegularCustomers[CustomerID],
SelectedCustomerType = "Distributor", Distributors[DistributorID],
SelectedCustomerType = "Partner", Partners[PartnerID]
)CustomerName =
SWITCH(
TRUE(),
SelectedCustomerType = "Regular Customer", RegularCustomers[CustomerName],
SelectedCustomerType = "Distributor", Distributors[DistributorName],
SelectedCustomerType = "Partner", Partners[PartnerName]
)Add the BookingAmount measure and the Rank measure.
Apply the Top N filter using the slicer value.
DAX
TopNFilter =
IF(
[Rank] <= SELECTEDVALUE(TopNValues[Value]),
1,
0
)Apply this filter to the matrix visual to show only the top N customers.
Hi Anonymous ,
Thank you for reaching out to Microsoft fabric community forum.I also want to thanks bhanu_gautam for valuable input the suggested approach involving dynamic measures for calculating Booking Amount, implementing Top N filtering, and generating rankings is correct.
However, I would like to have a clarification regarding the use of SELECTEDVALUE() within calculated columns:
To implement dynamic switching in the matrix visual based on slicer selection (e.g., Customer Type), consider the following approach:
- Create a unified table using the UNION function that consolidates all relevant customer types along with their IDs and Names.
- Use measures (rather than calculated columns) to implement dynamic logic for both display and filtering including the Top N logic.
This method ensures the matrix visual responds correctly to slicer selections and maintains optimal performance and flexibility.
Hope this helps. Please reach out for further assistance.
If this post helps, then please consider to Accept as the solution to help the other members find it more quickly and a kudos would be appreciated.
Thank you.
Hi @krishna_1811 ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.
