Forum Discussion

Shashanth's avatar
Shashanth
Frequent Visitor
5 years ago
Solved

Need help on a Measure

Hi All,   I am trying to get the Product_id_count with a few filters. But which ever method I try, I am not able to get it.     I have 2 summarized tables ; calculated_Product_rating_table and c...
  • Shashanth's avatar
    Shashanth
    5 years ago

    Guys, I was able to find a solution. A collegue of mine helped me.

     

    Basically we created an other table WITHOUT USING SUMMARIZE(I did not know this was possible/allowed)

     

    Final_Table = DISTINCT(calculated_Product_rating_table[product_id])
     
    and added all the columns that I wanted to add a filter on,
     
    [Overall Repeat___rate >= 300%] = LOOKUPVALUE(calculated_service_rating_table[Overall Repeat >= 300%],calculated_Product_rating_table[product_id],Final_Table[product_id])
     
    Overall_Approval___Rate>60% = LOOKUPVALUE(calculated_Product_rating_table[Overall_Approval__Rate>60%],calculated_Product_rating_table[product_id],Final_Table[product_id])
     
    [TV Repeat___rate >= 150%] = LOOKUPVALUE(calculated_service_rating_table[TV Repeat >= 150%],calculated_Product_rating_table[product_id],Final_Table[product_id])
     
    TV_Approval___Rate>50% = LOOKUPVALUE(calculated_Product_rating_table[TV_Approval__Rate>50%],calculated_Product_rating_table[product_id],Final_Table[product_id])
     
    And finally created a column with an OR function, that I finally wanted
     
    Required product_id's = or(Final_Table[Overall Repeat___rate >= 300%]=true,or(Final_Table[TV_Approval___Rate>50%]=true,or(Final_Table[Overall Repeat___rate >= 300%]=true,Final_Table[TV Repeat___rate >= 150%]=true)))
     
    and created a measure on it
     
    product_id_count = CALCULATE(DISTINCTCOUNT(Final_Table[product_id]),Final_Table[Required FSN's]=true)
     

     

    Thanks to him. FInally I was able to do it.
     
    Regards
    -Shashanth
amitchandak's avatar
amitchandak
Super User
5 years ago

Shashanth , Try like

 

product_id_count = CALCULATE(DISTINCTCOUNTNOBLANK(calculated_Product_rating_table[product_id]),filter(values(calculated_Product_rating_table[product_id]), OR(or(calculated_Product_rating_table[Overall_Approval__Rate>60%]=TRUE,calculated_Product_rating_table[TV_Approval__Rate>50%]=TRUE),or(LOOKUPVALUE(calculated_service_rating_table[Overall Repeat >= 300%],calculated_Product_rating_table[product_id],calculated_service_rating_table[product_id])=TRUE,LOOKUPVALUE(calculated_service_rating_table[TV Repeat >= 150%],calculated_Product_rating_table[product_id],calculated_service_rating_table[product_id])=TRUE))))

 

or

 

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

  • Shashanth's avatar
    Shashanth
    Frequent Visitor
    5 years ago

    Hi Amit,

     

    Thanx for your response.

    I am not able to get this working.

     

    Still not sure what the error is.

     

    And wrt sharing a sample file, I am not able to do it since it is a direct query mode to the servers with lot of data.

     

    Is there any ther solution that you can help me out with?