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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
posterme
Frequent Visitor

Slicer - How can I count selected months?

Hi Guys,

 

how can I or is it possible to count how many months I have selected in a Date-Slicer? I want to divide a value with the count of monts, e.g. earnings / selected months ... All variants do not show me the correct number of months - only distinctcount brings 12 months 😉

 

Thanks in advance

13 REPLIES 13
ChrisHawker
New Member

Months Counted in Slicer =
IF(
    ISCROSSFILTERED('Date Table'[Month]),
    DISTINCTCOUNT('Date Table'[Month])
)
FCF
Helper IV
Helper IV

hi

I have also tried this solution using "ISFILTERED" or "ISCROSSEDFILTERED" and both options only 

result in 0. I have two items selected in my slicer. Regards

slicer.JPG

vega
Resolver III
Resolver III

Count = 
IF(
    ISFILTERED(Months[Month]),
    COUNT(Months[Month]),
    0
 )

This will count if the months column is selected directly. If you want to account for the months column being filtered indirectly, use ISCROSSEDFILTERED instead of ISFILTERED.

posterme
Frequent Visitor

Hi Vega,

 

thank you - unfortunately, the value is always "0" instead of, in this example, "5":

 

2018-02-13 19_26_02-PIMPowerBI_PO_CRMOnline - Power BI Desktop.png

 

BR

Can you post your Date table? How is the slicer being created? Like I said earlier, if the months are not being filtered directly, then you need to use ISCROSSFILTERED.

posterme
Frequent Visitor

Hi,

 

I have tested both ISFILTERED and ISCROSSFILTERED - below you'll find the screenshot from the table field:

 

2018-02-13 19_41_41-PIMPowerBI_PO_CRMOnline - Power BI Desktop.png

 

Slicer (Timeline) is configured only with the "Date" field:

 

2018-02-13 19_43_44-PIMPowerBI_PO_CRMOnline - Power BI Desktop.png

 

Again - many thanks for your support 🙂

 

There is certainly more optimize way to do it.

But for example you can duplicate your date column convert it into a type text, and concatenate the year and the month.

And then do a DISTINCTCOUNT of your new column.

 

Example of M query to get your year and month

Text.Range([Date], 6,4) & Text.Range([Date], 3, 2)

i too would like to know how to do this

Hi cylix,

 

till now I do not have a proper solution for that...

Hi, have you had any luck with this? I am tasked with doing something similar to this as well but the above solutions provided do not work. Please advise.

 

Thank you. @posterme 

Does your date table have a months column?

posterme
Frequent Visitor

Hi, its only a column defined as date field...

 

UPDATE:

 

And as I written in my first message,

 

with COUNT('TABLE'[Date].[Month]) the Value is 1096,

with DISTINCTCOUNT('TABLE'[Date].[Month]) it is 12 ...

 

But what is "1096" ? 🙂

I believe 1096 is the number of dates in your date column

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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