Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gillyr7
Frequent Visitor

Calculate From Another Table And Apply Filters From Other Table

I have two tables. My base table is using my PClaims table to calculate a median price by Affiliation and Code which I have in my formula shown below. It works well, except I have a problem when trying to apply report filters.

 

I have my filters working with the PClaims table. The PClaims table is filtering and the PClaims[Price] is filtering correctly, but my base table with my column formula below does not change. Is there a way to have my base table update when the PClaims table is filtered?

 

CALCULATE(median(PClaims[Price]),Filter(PClaims,PClaims[AffiliationLevel_II]=CommunityProf[Affiliation Level II]),FILTER(PClaims,PClaims[Code]=CommunityProf[Code]))

For reference, my base table is just Affiliation, Code, and the column calculated from the formula. My PClaims table has more fields that are being used for filters like year, and a flag for in and out of network. 

8 REPLIES 8
parry2k
Super User
Super User

@gillyr7 seems like you have one to many relationship between base table and pclaims,correct? Can you share sample data and expected result?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

I will have to work on getting a same dataset. My original is huge so it will take sometime to gather a sample.

 

My PClaims table has the following fields: Affiliation_II, Affiliation_III, Payer, Year, Network, Code and Price.

 

My Base Table (Community_Prof) has the fields: Affiliation_II, Code, and Price using the median formula above. I have this Base table as a new table than the PClaims because the PClaims is the true data, where not all Affiliation_II's have the same exact codes. I created my base table so that they would all have the same fields, and that is why I am just pulling the median from it. (if it is missing I take the overall median). 

 

I can filter the PClaims table, but the median from my Base table is not filtering down correctly. It is a many to many relationship based on Affiliation_II and Code.

@gillyr7 seems like you created base table using DAX expression by selecting New Table option, correct?

 

If that is the case, your base table will not filter based on slicer selection. It will always create the base table full dataset available in PClaims. 

 

Question, why you want to create this seperate base table?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Below is a screenshot of a very simple example of what I am trying to do. The PClaims is the raw data. Notice in yellow, B is missing Code "2". In my base table, I have it set up so that each affiliation_II has each code, so there is a line for Affiliation_II B and Code "2". The reason I have this Base table instead of just using PClaims is so that each Affiliation_II has every single code, even if it is missing in the raw data.

 

 

Sample Table.JPG

Hi @gillyr7 ,

 

Please confirm my below understanding is correct:

 

1. There're 2 tables both created under query editor, Base table is not the calculated table of Pclaims.

2. You'd like to make sure each Affiliation_II has every single code,  but how does the code 2 of B come from? could you please clarify?

3. The price column in basetable is a measure, but the "50" makes me confused coz code 2 doesn't appear in the pclaims table. how does the "50" come from?

4. Need to know the detailed question and the expected result you wanna get.

 

I'd like to provide the further support once get the clarification.

 

Best regards,

Dina Ye

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

@gillyr7 what is =CommunityProf table?

 

Are you creating base table using DAX expression you put in your original post?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

No, this is Base Table is created using Power Query. In PowerQuery, I have a table with every code, and I duplicate a PClaims query and group by the Affiliation_II. Then I merge these two together with all rows from both to get every Affiliation_II with every code.

The base table is CommunityProf. I was calling it the base table because that is where the formula was residing.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.