Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi PBI Community,
My problem is not that simply to explain without writting a long message. I'll try to give you just enough details but not too much 😀
My problem is the next part of that one :
I have a big database (hundreds of thousands of lines) corresponding to articles received in a warehouse. To simplify it, let's say that I juste have those colums :
- Date of receiving
- reference number of the receiving
- Article
- Article version (A, B, C, etc...) and many others columns on which I want to apply slicers for my report
NB1 :
1 Receiving reference usually concerns many articles (let's imagine it is a truck delivering many articles)
NB2:
I can have more than once the same line repeating, it means that I have received more than once the same article with the same receiving reference.
My first goal was to create a column in my database calculating, for each line, the distinct number of receiving references received in the same month for the same article.
Here is the formula used, and it works :
Distinct number of receiving references =
CALCULATE (
COUNTROWS ( DISTINCT ( 'Table'[reference number of the receiving] ) ),
ALLEXCEPT ( 'Table', 'Table'[Article], 'Table'[Month] )
)
Now, I want in my report a chart illustrating, month by month, the number of distinct articles received with more than one receiving reference. So I have created a measure :
It works, but with this way, I cannot apply slicers correclty on my report. For example if I want to chose the "Version of article" as slicer, the result might be wrong.
Example :
Date | Receiving reference | Article | Version | Distinct number of reference for the month |
Feb | #11 | Apple | A | 2 |
Feb | #12 | Apple | A | 2 |
Feb | #12 | Apple | B | 2 |
Feb | #13 | Tomato | A | 1 |
March | #15 | Apple | A | 1 |
My chart will display :
But if I add a slicer on my report for chosing the "Version", the result will always be the same, no matter my slicer choice (by chosing Version = B, Feb should be 0, but it is still 1)
Any idea how to incorporate my additional filters (=slicers) in my result ?
Any help will really be appreciated, it makes me crazy 😵
Thanks a lot
Solved! Go to Solution.
Hi @sebastienke ,
Try the following measure to calculate the values you need:
Count above 1 =
COUNTROWS (
FILTER (
SUMMARIZE (
'Table';
'Table'[Article];
'Table'[Date];
"@Count@"; COUNTA ( 'Table'[Article] )
);
[@Count@] > 1
)
) + 0
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @sebastienke ,
Create a column with startofmonth for each datapoint (if your date column is already in the date format the measure below needs to be changed), add a calendar table and relate to that startofmonth then add the following measure and use the months in the calendar table for your visual:
Count above 1 + month =
CALCULATE (
[Count above 1];
FILTER (
ALL ( 'calendar'[Date] );
'calendar'[Date] <= MIN ( 'calendar'[Date] )
&& 'calendar'[Date] >= MINX ( 'calendar'; DATEADD ( 'calendar'[Date]; -1; MONTH ) )
)
)
Check PBIX file attach.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @sebastienke ,
You need to have an extra filter so that you can deselect the values that are not present in the current month so the measure should be redone to this:
Received 2+ in 2 months =
IF (
MIN ( 'calendar'[Date] )
= CALCULATE ( MIN ( 'calendar'[Date] ); ALL ( 'calendar' ) );
"Last Month data Missing";
CALCULATE (
COUNTROWS (
FILTER (
SUMMARIZE (
'Table';
'Table'[Article];
"@Count2+2m@"; COUNTROWS ( DISTINCT ( 'Table'[Receiving reference] ) )
);
[@Count2+2m@] >= 2
)
);
FILTER (
ALL ( 'calendar'[Date] );
'calendar'[Date] <= MIN ( 'calendar'[Date] )
&& 'calendar'[Date] >= MINX ( 'calendar'; DATEADD ( 'calendar'[Date]; -1; MONTH ) )
);
FILTER (
SUMMARIZE (
'Table';
'Table'[Article];
"@Count2+2m@"; COUNTROWS ( DISTINCT ( 'Table'[Receiving reference] ) )
);
[@Count2+2m@] > 0
)
) + 0
For the text part you need to add the following measure.
Articles selected =
SWITCH (
TRUE ();
MIN ( 'calendar'[Date] )
= CALCULATE ( MIN ( 'calendar'[Date] ); ALL ( 'calendar' ) ); "";
[Received 2+ in 2 months] = 0; "Nothing";
CONCATENATEX (
FILTER (
SUMMARIZE (
'Table';
'Table'[Article];
"@Count2+2m@"; COUNTROWS ( DISTINCT ( 'Table'[Receiving reference] ) )
);
[@Count2+2m@] >= 2
);
'Table'[Article];
","
)
)
Believe this get's the result you need.
Check PBIX file attach.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi,
Share a simple dataset and show the result you are expecting.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks @MFelix for the proposing, but it doesn't solve my problem.
If I add the "version" to the ALLEXCEPT function, the problem is similar to the first one. Here is what I have :
BEFORE :
AFTER:
So when I use my measure to calculate, month by month, the number of distinct articles received with more than one receiving reference, the result is right, no matter the version slicer I choose.
Slicer = all
Slicer = Version A
Slicer = Version B
But it works only for this example.
Let's say I have the following database instead of the first one (1 line is missing) :
In that case my result will be ok when I choose slicer = A or slicer = B but not when slicer = all (result will be 0 for February instead of 1)
I think my error is to calculate the column "Distinct number of receiving reference" directly in my database, for each line. I think I need to incorporate this in a measure, but I don't know how to do it. Any idea ?
Thanks for you help !
Hi @sebastienke ,
Try the following measure to calculate the values you need:
Count above 1 =
COUNTROWS (
FILTER (
SUMMARIZE (
'Table';
'Table'[Article];
'Table'[Date];
"@Count@"; COUNTA ( 'Table'[Article] )
);
[@Count@] > 1
)
) + 0
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsMany thanks @MFelix
It is so obvious now that I know the solution.... Summarize.... that was the key.
Actually I did a little bit correction to obtain the result I wanted, but the idea was here.
Hi @MFelix
Can you help me one more time abouth the same topic ?
If I want to use the same measure in my report to illustrate the number of distinct articles received with more than one receiving reference not month by month, but for 2 rolling months (result of March = result for March + February )
Example
Date | Receiving reference | Article | Version |
Feb | #11 | Apple | A |
Feb | #12 | Apple | A |
Feb | #12 | Apple | B |
Feb | #13 | Tomato | A |
March | #15 | Tomato | A |
March | #15 | Apple | A |
March | #25 | Tomato | A |
March | #28 | Apple | B |
April | #35 | Peach | A |
April | #37 | Peach | B |
April | #38 | Tomato | B |
April | #40 | Tomato | B |
In that case the result will be (without any slicer on the version):
Date | number of distinct articles received with more than one receiving reference for 2 rolling months |
Feb | January data missing + 1 |
March | 3 (1 for Feb and 2 for March) |
April | 4 (2 for March and 2 for April) |
I tried different things but without any success.
Do you have any idea how to do it ?
Thanks a lot for your hep
Hi @sebastienke ,
Create a column with startofmonth for each datapoint (if your date column is already in the date format the measure below needs to be changed), add a calendar table and relate to that startofmonth then add the following measure and use the months in the calendar table for your visual:
Count above 1 + month =
CALCULATE (
[Count above 1];
FILTER (
ALL ( 'calendar'[Date] );
'calendar'[Date] <= MIN ( 'calendar'[Date] )
&& 'calendar'[Date] >= MINX ( 'calendar'; DATEADD ( 'calendar'[Date]; -1; MONTH ) )
)
)
Check PBIX file attach.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsBRILLIANT @MFelix
I would have never thought about that solution !
Last question :
If I want to add the filter that select only the articles receveid in the month observed, how do I do that ?
For example :
If I have :
And if I want to observe February, I should see as result that I have receveid just 2 articles more than once for the 2 rolling months: tomatos with 3 distinct receiving and peaches with 3 distinct receiving, but not apples because this sku was not received in February (although it was received twice in January).
That would be my last question.
Many many thanks for your help.
Hi @sebastienke ,
Not really sure if I'm uderstanding your question but if you place a slicer for the month and another for the articles that should give you the values you need.
Can you please elaborate a little bit more on what is the interaction you are trying to achieve?
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix
Alle the solutions we've talked in this topic allow me to :
each month, count the number of articles which have more than 1 distinct receiving references during a period of two months (month n and n-1)
So, if I have received apples 3 times in January, and 0 in February, and if I take a look on February, our formula will take into account the apples. But because apples have not been received in February, I don't want to take them into account.
Example :
Here is the database :
Date | Receiving reference | Article | Version |
1 | 8 | Peaches | A |
1 | 9 | Peaches | B |
2 | 10 | Apple | B |
2 | 11 | Apple | A |
2 | 11 | Apple | A |
2 | 11 | Tomato | A |
2 | 12 | Tomato | B |
3 | 13 | Tomato | A |
3 | 14 | Tomato | A |
4 | 15 | Apple | A |
Here is the result of the current calculations :
Date | Result | Comments |
1 | ? | last month data missing |
2 | 3 | peaches, apple, tomato |
3 | 2 | apple, tomato |
4 | 1 | tomato |
Here is the result I'd like to obtain :
Date | Result | Comments |
1 | last month data missing | |
2 | 2 | apple, tomato |
3 | 1 | tomato |
4 | 0 | nothing |
Thanks a lot 🙂
Hi @sebastienke ,
Sorry for asking this but based on your measure and taking into account that you are filtering the counts above 2 the final result you presente is incorrect:
I did a summarize table with your measure and got the following table below
Article | @Count2+2m@ | Month |
Apple | 2 | 2 |
Apple | 2 | 3 |
Tomato | 2 | 3 |
Tomato | 2 | 4 |
Peach |
2 | 4 |
so final result would be:
1 - 0
2 - 1 product (apple)
3 - 2 product (Apple, tomato)
4 - 3 product (Apple, Tomato, peach)
5 - 2 product (Tomato, Peach)
Can you confirm this analysis?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsI'm not sure you used the last example of my last topic.
The first picture is the result of my measure (the one which is wrong and that I want to correct), when displayed month by month. It corresponds to my last example. The others pictures are the different parameters used to let you know.
Thank you again
Hi @sebastienke ,
You need to have an extra filter so that you can deselect the values that are not present in the current month so the measure should be redone to this:
Received 2+ in 2 months =
IF (
MIN ( 'calendar'[Date] )
= CALCULATE ( MIN ( 'calendar'[Date] ); ALL ( 'calendar' ) );
"Last Month data Missing";
CALCULATE (
COUNTROWS (
FILTER (
SUMMARIZE (
'Table';
'Table'[Article];
"@Count2+2m@"; COUNTROWS ( DISTINCT ( 'Table'[Receiving reference] ) )
);
[@Count2+2m@] >= 2
)
);
FILTER (
ALL ( 'calendar'[Date] );
'calendar'[Date] <= MIN ( 'calendar'[Date] )
&& 'calendar'[Date] >= MINX ( 'calendar'; DATEADD ( 'calendar'[Date]; -1; MONTH ) )
);
FILTER (
SUMMARIZE (
'Table';
'Table'[Article];
"@Count2+2m@"; COUNTROWS ( DISTINCT ( 'Table'[Receiving reference] ) )
);
[@Count2+2m@] > 0
)
) + 0
For the text part you need to add the following measure.
Articles selected =
SWITCH (
TRUE ();
MIN ( 'calendar'[Date] )
= CALCULATE ( MIN ( 'calendar'[Date] ); ALL ( 'calendar' ) ); "";
[Received 2+ in 2 months] = 0; "Nothing";
CONCATENATEX (
FILTER (
SUMMARIZE (
'Table';
'Table'[Article];
"@Count2+2m@"; COUNTROWS ( DISTINCT ( 'Table'[Receiving reference] ) )
);
[@Count2+2m@] >= 2
);
'Table'[Article];
","
)
)
Believe this get's the result you need.
Check PBIX file attach.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @sebastienke ,
Can you please confirm what is the measure you are currently using for the calculation of the quantities?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português
Here is the measure :
After my test, it seems that there is no problem you said.