cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
anto0351
Helper I
Helper I

Measure to exclude values if number in the column is below the number of dates present

Hey Guys,

 

I am stuggeling to find a way to exclude items which do not have data in all the cells represented by the data range.

 

I am therefore looking for a measure which I can include in my current measure to make sure that the following table will only include the columns with the headers: 1, 3, and 5 and thereby remove 2 and 4. The measure should be dynamic and adapt to the change of the time line slicer meaning that if the number of rows (data points) for e.g. column 2 is below the number of rows with dates, the measure should exclude this column completely.

 

Screenshot of the table:

2021-01-06 13_23_59-Samlet overblik - Aon.png

 

If of any help, my current measure look like this: 

"=CALCULATE(PRODUCTX('tbl_Afkast_samlet', 1 + 'tbl_Afkast_samlet'[Afkast]),FILTER(ALLSELECTED('tbl_Dato'[Date]),'tbl_Dato'[Date] <= MAX('tbl_Dato'[Date])))*100"

 

I look forward to hearing your solutions.

 

Best regards,

Anton

7 REPLIES 7
AlB
Super User III
Super User III

@anto0351 

For Excel, I do not know exactly how what is shown in the pivot table affects what is shown in the slicer. I mean there seems to be an underlying process for that but I am unaware of the inner workings. I would suggest you create a separate posting to ask about that.  

 

In Power BI the behaviour is different and this could be easily done by applying the measure we use in the pivot table also to the slicer through a visual filter but this cannot be done in Excel. 

 

As for Excel vs Power BI, can you give me a (simple) example of what you do with the charts  for its use in PowerPoint that cannot be done in PBI? Just curious

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

AlB
Super User III
Super User III

@anto0351 

That behaviour in Excel seems to be different from the standard behaviour in PBI. However, I believe you can get what you want by going to Slicer settings (for the Company slicer) and playing with the options in the red rectangle in the pic below. You probably want to have it as it is shown in the pic. I am curious: why do you work with Excel-PowerPivot instead of PowerBI?

 

image.png

 

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

@AlB 

 

Thanks for the solution, which is in my case however is not very desireable. This comes down to:

 

  1. This is (still) making it impossible for you to tell which companies actually has data that fits the criteria in relation to the selected dates.
  2. In the case where I have a lot more companies that this, the once without data will take up a lot of space from the ones actually possible to be seen in the table.

Therefore, I need to run the following settings (picture below) as you should only be able to select the ones with data and if they have data you must be able to select them - as the additional issued raised after using the measure shows.

 

slicer.png

 

If this is what I am trying to accomplish, do you by any means have another solution to this issue? Alternatively, a different way of using the measure of another measure? I really appreaciate your help in what seems to be a larger issue than first interpreted! 🙂

 

The reason, why I am using Excel-PowerPivot instead of PowerBI simply comes down of the lack of opportunity in PowerBI to add your graphs, tables, etc. from PowerBI in a dynamic view in PowerPoint and PDF files. Here, the only build-in opportunity is to add your visuals as screenshots, which means that you cannot change anything in relation to the data/view when doing the presentation in PowerPoint. As the analyses I prepare in Excel in the end must be used by others or myself for customers, they must be presented in PowerPoint and as the functionaly is lacking, I am stuck with using Excel-PowerPivot . In the excel part however, I agree that PowerBI seems to offer a better overall solution as this is by no means the first case that works/acts differently in PBI than in Excel..

AlB
Super User III
Super User III

@anto0351 

You are right. I wanted to simplify and I messed it up. Try this:

 

New measure V2 =
VAR dates_ = ALLSELECTED ( Table1[Date] )
VAR check_ =
    COUNTROWS ( FILTER ( dates_; NOT ISBLANK ( [Current_measure] ) ) ) = COUNTROWS ( dates_ )
RETURN
    IF ( check_; [Current_measure] )

 

By the way, it'd be better to have a date table

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

Hi @AlB ,

 

Thank you for the new measure - it surely seems to do the trick 🙂

 

However, I have noticed something, which I cannot make sense of in relation to the measure and the filtering done by the slicer. When selecting the entire period (as it can be seen in screenshot 1) the filter for "Company" - the slicer - shows the correct companies which are also shown in the table (1,3,4,7, and 8). However, if I adjust the date slicer to e.g. 30-09-2020 to 30-11-2020 (screenshot 2 below) the slicer/filter for "company" do not match the companies actually present in the table. The slicer shows again (1,3,4,7, and 8 ) but in the table, you can see that there are actually more companies present (1,2,3,4,6,7,8, and 9).

 

Screenshot 1 - entire period (slicer for company = companies in table):

all period.png

 

Screenshot 2 - period 30-09-2020 to 30-11-2020 (slicer for company ≠ companies in table):

shorter period.png

 

As the filtering with both the date timeline and the slicer filter "company" is essential for what I am trying to accomplish, I hope you can find a solution where the funtionality remains (as your measure surely does the trick in this way) but the slicer shows all companies present in the table.

 

If my screenshots are not clear enough - you can easily recreate it in the sample workbook I send above.

 

I hope you can figure it out.

 

/Anton

AlB
Super User III
Super User III

Hi @anto0351 

Try this, where [Current measure] is the measure you show above:

New measure =
VAR aux_ =
    CALCULATE (
        LASTNONBLANK ( 'tbl_Dato'[Date], [Current measure] ),
        ALLSELECTED ( 'tbl_Dato'[Date] )
    )
RETURN
    IF ( NOT ISBLANK ( aux_ ), [Current measure] )

 If this doesn't work, share the pbix (with dummy data if necessary)

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

Hi @AlB ,

 

Thank you for the proposed measure, however I was not successful in implementing it. 

 

I have therefore prepared a workbook with dummy data, just as you propsed! Here, I have added both the "current measure" and the measure you propsed as "AIB_proposed_measure" in the table to showcase that the results are completely identical. This highlights that the new measure does not work.

 

Link to the download of the workbook via google drive: https://drive.google.com/file/d/1SN2dJVlrQ0tuXNSKf6NvgOusDYG6_mYc/view?usp=sharing 

 

I look forward to seeing what you can come up with for my power query issue.

 

Thank you in advance,

Anton

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors