Forum Discussion
Matrix visual with rows from different dimension tables
Dear community,
Hope you can help me with solving my case I faced and would like to figue out with it.
I have star-schema model (part of it on screenshot).
I would like to create a Matrix visual that will contain rows from "license_id" column of "dim_licenses" table as parent rows and "expense_id" column of dim_expenses table as nested rows (child) to see only these expenses where license_id and expense_id matched in fact table.
It seems like it works once I set bi-directional relations between fct and dim tables, but as I know using bi-directional relationship is not a good practise.
Is there any way how to create such visual without using bi-directional relationships?
Many thanks in advance.
Kind Regards,
Hi DzmitrySh ,
You can create a measure as below to get it, please find the details in the attachment.
Count of expenses = VAR _sellicenseid = SELECTEDVALUE ( 'dim_licenses'[license_id] ) VAR _selexpenseid = SELECTEDVALUE ( 'dim_expenses'[expense_id] ) RETURN CALCULATE ( COUNT ( 'fct_license expensexx'[expense_id] ), FILTER ( 'fct_license expensexx', 'fct_license expensexx'[expense_id] = _selexpenseid && 'fct_license expensexx'[license_id] = _sellicenseid ) )If the above one can't help you, please provide some raw data in your table (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:
How to provide sample data in the Power BI Forum
How to Get Your Question Answered Quickly
And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
1 Reply
Hi DzmitrySh ,
You can create a measure as below to get it, please find the details in the attachment.
Count of expenses = VAR _sellicenseid = SELECTEDVALUE ( 'dim_licenses'[license_id] ) VAR _selexpenseid = SELECTEDVALUE ( 'dim_expenses'[expense_id] ) RETURN CALCULATE ( COUNT ( 'fct_license expensexx'[expense_id] ), FILTER ( 'fct_license expensexx', 'fct_license expensexx'[expense_id] = _selexpenseid && 'fct_license expensexx'[license_id] = _sellicenseid ) )If the above one can't help you, please provide some raw data in your table (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:
How to provide sample data in the Power BI Forum
How to Get Your Question Answered Quickly
And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards