cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rigosakh Frequent Visitor
Frequent Visitor

Filtering two tables from the same query

Hi all, 

 

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

 

Cheers

1 ACCEPTED SOLUTION

Accepted Solutions
rigosakh Frequent Visitor
Frequent Visitor

Re: Filtering two tables from the same query

5 REPLIES 5
avisingh Regular Visitor
Regular Visitor

Re: Filtering two tables from the same query

Rigosakh,

 

I recorded a video response for you :-)
http://www.youtube.com/watch?v=kU_1sreC4R0 (see also attached file)

 

 

⚡Power On!⚡
-Avi Singh. Microsoft MVP. PowerBIPro

Power BI Tutorial for Beginners (Step-by-Step) and more on YouTube.com/PowerBIPro

rigosakh Frequent Visitor
Frequent Visitor

Re: Filtering two tables from the same query

Hi Avi, 

 

Thank you for the fast reply and an informative video. I can imagine your confusion Smiley Happy 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):

 

Original data.PNG

 

Then using SUMMARIZECOLUMNS I create a new table to count how many times in total each product was registered per daysummarizecolumns output.PNG

 

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. 

rigosakh Frequent Visitor
Frequent Visitor

Re: Filtering two tables from the same query

Community Support Team
Community Support Team

Re: Filtering two tables from the same query

Hi @rigosakh,

 

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?

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
rigosakh Frequent Visitor
Frequent Visitor

Re: Filtering two tables from the same query

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 ?