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
newgirl
Helper V
Helper V

Generate series or table based on measures

Hi! 

Below is my data for this report (text and photo provided).

 

eATW 2.JPG

 

ReferenceActionCompany CodeDateMonthYearMerged
900147675110Pick up500007761/3/202120210120210150000776
900147675211Pick up500007761/3/202120210120210150000776
900147675311Pick up500007761/3/202120210120210150000776
900147675511Pick up500007761/3/202120210120210150000776
900147674110Pick up500018421/3/202120210120210150001842
5.00063E+12Served500063261/27/2021 9:58:36 PM20210120210150006326
5.00087E+12Served500086641/28/2021 5:09:08 AM20210120210150008664
5.00059E+12Served500058681/27/2021 8:07:13 PM20210120210150005868
5.0009E+12Served500090011/28/2021 4:24:48 AM20210120210150009001
5.0009E+12Served500090011/28/2021 9:47:48 PM20210120210150009001
5.00093E+12Served500092741/29/2021 11:44:58 PM20210120210150009274
5.0009E+12Served500089571/28/2021 5:09:09 AM20210120210150008957
5.00093E+12Served500092741/28/2021 5:09:08 AM20210120210150009274
5.00063E+12Served500063171/29/2021 1:14:52 AM20210120210150006317
5.00016E+12Served500016101/28/2021 2:30:15 AM20210120210150001610
5.0003E+12Served500030221/31/2021 11:44:40 PM20210120210150003022
5.0003E+12Served500030221/31/2021 11:24:25 PM20210120210150003022

 

 

and here is my output matrix using the following measures:

 

Count of Served = CALCULATE(COUNT(SharePoint[Action]),SharePoint[Action]="Served")

Count of PU = CALCULATE(COUNT(SharePoint[Action]),SharePoint[Action]="Pick up")

% of eATW = [Count of Served]/[Count of PU]

Additional Raffle = IF([% of eATW]=1, 4,0)

TotalRaffleTickets = [Count of Served] + [Additional Raffle]

 

eATW 1.JPG

 

My desired additional table is below (column R in the photo). The generated rows (or series) are  based on the total raffle tickets computed. FYI that the 'merged' column is combined columns of MonthYear and the Company Code. 

 

Hope somebody can help me!

eATW 3.JPG

 

5 REPLIES 5
amitchandak
Super User
Super User

@newgirl , You will not able to generate a series of measures as this new table will not respond to the slicer values

hi @amitchandak  oh, I see. Nevertheless, how to proceed with generating the desired series of measures even though it won't respond to slicer values?  

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

Hi, @amitchandak !

 

Here is the link of sample data in sheet name 'sample'. In the 'pivot' sheet, it displays the desired output I need.

 

https://drive.google.com/file/d/1f7gV47oqlX-WNKDL_hECUIHNL6kDKM1E/view?usp=sharing

 

Thank you!

Hi, @newgirl 

I am afraid it is difficult to  use dax to expand multiple rows based on the value of a measure column.

 

If you still have no idea about your problem, you can open a support ticket with MSFT to seek technical support , usually they will have better suggestions.

 

Best Regards,
Community Support Team _ Eason

 

 

 

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.