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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ewelinag17
Frequent Visitor

new measure of the same row values ​​with filters applied

Dear,

I have a question.

I have two tables with excel connected by relations. Based on them, I created the table below in my report. The table contains all the information I need. Columns: Data, Index, Ba, in / out, Page number, material come from table 1. Column: Sh comes from table 2.

 

1600108133953_tempsnip.png

The report includes filters for: date, site number, material, and Sh. I'm having trouble creating a measure that will count the values ​​of in from the last row until it encounters the value of out. Out means the end of counting lines. Here, for my photo, the measure result should be 7.

Can it be solved with one measure?

Thank you in advance for your help.

 

7 REPLIES 7
amitchandak
Super User
Super User

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

@amitchandak 

Here are sample output tables.

ewelinag17_0-1600164595246.png

 

ewelinag17_1-1600164688461.png

And this is what it looks like in the report. Tables are connected by a BA column with a many-to-many relationship. I want to count 'in' from end until encountering 'out'.   '-' should be omitted and not counted.

ewelinag17_3-1600164826797.png

Can it be done with one measure? If so, will you help?

 

Greg_Deckler
Super User
Super User

@ewelinag17 Can you explain where the 7 is coming from? I can't figure out the logic to get 7. Is it the lines that are "in" and less than the Indeks of the "out"? If so, it should be something like:

 

Measure =
  VAR __Indeks = MAXX(FILTER('Table',[in/out]="out"),[Indeks])
RETURN
  COUNTROWS(FILTER('Table',[Indeks]<__Indeks && [in/out]="in"))

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler 

Here it is marked what the measure should count. The measure should display 10.

inout.png

@ewelinag17 OK, but why those rows? What is the logic behind it?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler 

"In" means the result is within some acceptable range and "out" means the result is out of range. I was given the task of creating a report that would count all the “exits” (I already did) and how many recent measurements are in range. It is about setting parameters for the machine, if the last result of "in" is 10, you can use it to produce the product.

But the measures I use don't count lines "in" to "out".

 

@ewelinag17 OK, I am pretty sure that I understand the words "in" and "out" are used to flag rows. That was not what I was asking. What I was asking is that I see rows in the tables that you posted that have the word "in"; the meaning of which you so eloquently explained in your last message, but are NOT counted or you do not want counted. You have other rows that have the word "in" that you DO want counted. So the flipping question is why do you want some "in" rows counted and some not to be counted? Is it because the Indeks of those "in" rows is larger the the Indeks of the "out" row? Are there multiple "out" rows? Do we have to find the maximum of the largest Indeks that has an "out" and then count all of the "in" rows that have an Indeks larger than that? Is that the logic?

 

I cannot believe you just explained the words "in" and "out" to me...


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors