I've created two tables from the same query - one table has data in the original format and the other one (auxiliary, I use it to simplify the calculation of some measures) with the same data but with some columns removed / pivoted. The problem is that the second table doesn't respond to filters that I've set up to control the first (original) dataset. I can't establish relationships between these tables because it is essentially the same data ("columns must have unique values" error) . Is there any way around that or the only way to fix the filtering issue is to change DAX formulas so it doesn't require the second table ?
If what I mean is unclear then I'll show the example of data later
Solved! Go to Solution.
Thank you for the fast reply and an informative video. I can imagine your confusion 🙂 The reason why I'm doing this unusual thing is because I'm terrible at DAX and struggle to create complicated DAX formulae. I'm migrating from doing analysis in Excel and some calculations that relied on extensive use of pivot tables are hard for me to translate into Power BI format. The second (simplified) table was used as a pivot table in excel. I got rid of referencing the query to create a secondary table and now use SUMMARIZECOLUMNS instead. However, the problem remains the same - the filter doesn't control a table created by SUMMARIZECOLUMNS
Please see the snapshot of original data below ("site" ranges between ~1-1000, time between 8am-6pm):
Then using SUMMARIZECOLUMNS I create a new table to count how many times in total each product was registered per day
Then I need to calculate how many 1 values in "frequency" there are, 2 , 3 etc . Then it is also used for another calculation. I'm relatively new to DAX and can't get my head around how to use SUMMARIZECOLUMNS in a measure without creating a new table.
You have resolved the original problem via the suggestion in above link you provided, right? If so, would you please accept your sharing solution so that others having similar concern can find the answer more easily?
Hi @v-yulgu-msft ,
Yes, I've solved the original problem and marked the post with a link as accepted solution. However, this solution caused another issue and I'm not sure whether I should create another topic with a new question or continue posting here ?
We're excited to announce our first cross-community 'Can You Solve These?' challenge!
Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!
Visit our Community Blog for articles, guides, and information created by fellow community members.
Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.