Forum Discussion
Solved
MULTIPLE FILTRATION
I have the data like this... YEAR SHORTNAME DEALER TYPE PRODUCT APR MAY JUN JUL AUG SEP OCT NOV DEC JAN FEB MAR TOT CATEGORY 20182019 AB A STD PROD 10 0 0 0 0 0 ...
v-chuncz-msft wrote:You may use the following measure.
Measure = COUNTROWS ( FILTER ( VALUES ( cab[DEALER] ), CALCULATE ( SUM ( cab[QTY] ) <= 1000 ) ) )it gives only true or false...
I applied this measure ... and works fine..
qty500 = CALCULATE (DISTINCTCOUNT('BREAKUP CABLES'[DEALER] ),FILTER ( ALL ( 'BREAKUP CABLES'[DEALER] ), 'BREAKUP CABLES'[sumqty]<=499))
Hi srkase ,
thanks for explaining clearly.
Here's the output from your provided data.
Here are the steps. I just unpivot the months and filtered the qty <> 0
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtABiSyUdJUcnEAHEwSEuQDIgyB9EGRoACVIwWANIc6hziFKsDqoVzkDCCd0KI1KtMMJnhQs2XxiTaoUxPitQA8oZzAepN4H7nxhsagDVTKzxpiQZb4bLeGccxqO4i1jCBKcvXGhqjaWRATgV0DDRwq2gXaKFW0G7RIsjoKiVaAkaT1miRYkDOqQmWiXaWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [YEAR = _t, SHORTNAME = _t, DEALER = _t, TYPE = _t, PRODUCT = _t, APR = _t, MAY = _t, JUN = _t, JUL = _t, AUG = _t, SEP = _t, OCT = _t, NOV = _t, DEC = _t, JAN = _t, FEB = _t, MAR = _t, TOT = _t, CATEGORY = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"YEAR", Int64.Type}, {"SHORTNAME", type text}, {"DEALER", type text}, {"TYPE", type text}, {"PRODUCT", type text}, {"APR", Int64.Type}, {"MAY", Int64.Type}, {"JUN", Int64.Type}, {"JUL", Int64.Type}, {"AUG", Int64.Type}, {"SEP", Int64.Type}, {"OCT", Int64.Type}, {"NOV", Int64.Type}, {"DEC", Int64.Type}, {"JAN", Int64.Type}, {"FEB", Int64.Type}, {"MAR", Int64.Type}, {"TOT", Int64.Type}, {"CATEGORY", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"YEAR", "SHORTNAME", "DEALER", "TYPE", "PRODUCT", "TOT", "CATEGORY"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Month"}, {"Value", "Qty"}}),
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Qty] <> 0))
in
#"Filtered Rows"
measures for the qty:
Qty<10 = CALCULATE(COUNT('Table'[DEALER]), 'Table'[Qty] <=10)Qty<20 = CALCULATE(COUNT('Table'[DEALER]), 'Table'[Qty] >10 && 'Table'[Qty] <=20)Qty>30 = CALCULATE(COUNT('Table'[DEALER]), 'Table'[Qty] >=30)Dear Mussaedna ,
I have used query editor to unpivot the data and applied the measure what you have given,,,
but couldnt get the result
If you will try it on the data you gave, it will work.
if it didn't work, you should at least explain why or what problem you have encountered so it can be resolved.
Thank you.
THIS WAS THE SAMPLE DATA
YEAR SHORTNAME DEALER TYPE PRODUCT CATEGORY MONTH QTY 20192020 CBE A STD A CABLE APR 1000 20192020 CBE A STD B CABLE APR 3000 20192020 CBE A STD A CABLE APR 1738 20192020 CBE A STD B CABLE APR 3000 20192020 CBE A STD A CABLE APR 3940 20192020 CBE B STD B CABLE APR 1000 20192020 CBE C STD A CABLE APR 1000 20192020 CBE C STD B CABLE APR 1800 20192020 CBE C STD A CABLE APR 3000 20192020 CBE D STD B CABLE APR 1000 20192020 CBE D STD A CABLE APR 1000 20192020 CBE D STD B CABLE APR 2869 20192020 CBE D STD A CABLE APR 3000 20192020 CBE E STD B CABLE APR 1000 20192020 CBE E STD A CABLE APR 872 20192020 CBE E STD B CABLE APR 4000 20192020 CBE F STD A CABLE APR 1000 20192020 CBE F STD B CABLE APR 2000 20192020 CBE G STD A CABLE APR 1000 20192020 CBE G STD B CABLE APR 1000 20192020 CBE G STD A CABLE APR 2000 20192020 CBE G STD B CABLE APR 6000 20192020 CBE H STD A CABLE APR 2000 20192020 CBE I STD B CABLE APR 2000 20192020 CBE I STD A CABLE APR 3877 20192020 CBE I STD B CABLE APR 4000 20192020 CBE J STD A CABLE APR 1000 20192020 CBE J STD B CABLE APR 2000 20192020 CBE J STD A CABLE APR 6000 20192020 CBE K STD B CABLE APR 1000 20192020 CBE K STD A CABLE APR 4000 20192020 CBE L STD B CABLE APR 1000 20192020 CBE L STD A CABLE APR 890 20192020 CBE M STD B CABLE APR 2000 20192020 CBE M STD A CABLE APR 6000 20192020 CBE M STD B CABLE APR 12000 20182019 CBE M STD A CABLE APR 6000 20182019 CBE M STD B CABLE APR 12000 20182019 CBE M STD A CABLE APR 1000 20182019 CBE M STD B CABLE APR 3000 20182019 CBE M STD A CABLE APR 1738 20182019 CBE M STD B CABLE APR 3000 20182019 CBE M STD A CABLE APR 3940 20182019 CBE L STD B CABLE APR 1000 20182019 CBE K STD A CABLE APR 1000 20182019 CBE K STD B CABLE APR 1800 20182019 CBE K STD A CABLE APR 3000 20182019 CBE I STD B CABLE APR 1000 20182019 CBE I STD A CABLE APR 1000 20182019 CBE I STD B CABLE APR 2869 20182019 CBE I STD A CABLE APR 3000 20182019 CBE J STD B CABLE APR 1000 20182019 CBE J STD A CABLE APR 872 20182019 CBE J STD B CABLE APR 4000 20182019 CBE A STD A CABLE APR 2000 20182019 CBE A STD B CABLE APR 6000 20182019 CBE A STD A CABLE APR 12000 20182019 CBE A STD B CABLE APR 6000 20182019 CBE A STD A CABLE APR 12000 The measure i have used to filter <1000,
Qty<1000 = CALCULATE(COUNT('cab'[DEALER]),cab[QTY] <=1000)the result i get isMonth Count of Dealer Qty<1000April 13 21I have kept distinct count of dealerResults are wrongIn a month a dealer might have taken items in many invoices... The sum we should check and get the total and then the measures should be applied..