Forum Discussion

gillyr7's avatar
gillyr7
Frequent Visitor
7 years ago

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. 

Need Help

8 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    7 years ago

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

    • gillyr7's avatar
      gillyr7
      Frequent Visitor
      7 years ago

      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.

      • parry2k's avatar
        parry2k
        Icon for Super User rankSuper User
        7 years ago

        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?