datamodel Tracker

Re: Service principal Profiles

datamodel — Sat, 07 Feb 2026 20:31:13 GMT

Yes, very unfortunate. The way we're planning to work around this for now is to create the workspace, profile, and all items using a Service Principal, then use the profile to remove the SP. "JIT Admin"

Re: Share your thoughts on DirectQuery for Power BI datasets and Azure Analysis Services (preview)

datamodel — Thu, 30 Mar 2023 17:04:03 GMT

Got some inside info on this, and it's probably because some of the older workspaces are being 'upgraded'. Try creating a new workspace for the 'source' dataset.

Re: Share your thoughts on DirectQuery for Power BI datasets and Azure Analysis Services (preview)

datamodel — Fri, 17 Mar 2023 15:33:36 GMT

We are experiencing the same issue. Any insight would be appreciated! Thank you!

Re: Share your thoughts on DirectQuery for Power BI datasets and Azure Analysis Services (preview)

datamodel — Wed, 27 Apr 2022 18:09:39 GMT

Ok great! Thank you for the quick response.

Re: Share your thoughts on DirectQuery for Power BI datasets and Azure Analysis Services (preview)

datamodel — Wed, 27 Apr 2022 16:48:52 GMT

Is there an 'issue tracker' we can check to know this has been fixed?

Re: Define the slicer background-color in custom theme

datamodel — Mon, 07 Jun 2021 13:29:30 GMT

There is also another undocumented option: "foregroundNeutralSecondary".

Re: Date Slicer not showing the select the type of slicer check mark Icon on Power BI Service

datamodel — Wed, 09 Dec 2020 18:08:10 GMT

I don't think it's a bug per se, I just think they didn't take this into consideration. There is an idea we can all go vote on 🙂
Microsoft Idea · User can change online date slicer from relative to between (powerbi.com)

And one here:
Microsoft Idea · Date slicer: enable the dropdown menu in PBI service (powerbi.com)

Re: Date Slicer not showing the select the type of slicer check mark Icon on Power BI Service

datamodel — Fri, 16 Oct 2020 00:53:39 GMT

Hi OlgaHart , I wasn't so lucky to get this working. Did the dropdown to change between relative and between show up in the Reading view, in the service for you?

Re: Cumulative Sum over Different periods / Units

datamodel — Mon, 10 Feb 2020 11:56:21 GMT

How about cross joining to a summarized dimDate table to apply the value to each end date for each year?
You'll end up with a contractTable, dimDate, and a virtual contractTableExtended that is a cross join between the two. The relationship will be between dimDate[Date] and contractTableExpanded[maxDimDate]

contractTableExpanded =
ADDCOLUMNS (
FILTER (
CROSSJOIN ( contractTable, SUMMARIZE(dimDate,dimDate[Year],"lastDate",LASTDATE(dimDate[Date]) )),
[lastDate] >= contractTable[Start]
&& [lastDate] <= contractTable[End]
),
"maxDimDate", [lastDate]
)

Re: Cumulative Sum over Different periods / Units

datamodel — Thu, 30 Jan 2020 03:33:11 GMT

How's this?

1. Calculated Table that cross joins the contract table with a generated series of years from 2020 to 2030

contractTableExpanded = var Years = SELECTCOLUMNS(GENERATESERIES(2020,2030,1),"Year",[Value]) RETURN ADDCOLUMNS ( FILTER ( CROSSJOIN ( contractTable, Years ), [Year] >= contractTable[Start].[Year] && [Year] <= contractTable[End].[Year] ), "YearName", [Year] )

2. Measure to sum the Units

Units = CALCULATE(SUM('contractTableExpanded'[Units per Year]))

3. Measure to sum cumulative units

Cumulative Units = IF ( ISBLANK ( [Units] ), BLANK (), CALCULATE ( [Units], FILTER ( ALL ('contractTableExpanded'[Year] ), 'contractTableExpanded'[Year] <= MAX('contractTableExpanded'[Year]) ) ) )

Re: Define the slicer background-color in custom theme

datamodel — Sat, 02 Nov 2019 02:08:56 GMT

Thanks zapppsr , gaccardo already mentioned it above, and it is a great resource; however there are no mentions of changing "date input" properties of a slicer.

Re: Define the slicer background-color in custom theme

datamodel — Tue, 22 Oct 2019 06:38:48 GMT

I'm also looking to change color of Date Inputs and Slider of the Slicer visual.

Re: Crossjoin, GENERATE?

datamodel — Wed, 16 Oct 2019 04:42:04 GMT

I'm at a point where I now have the desired results from a calculation perspective, but I had to create a new calculated table which is costing storage. At least it works :) Can't seem to make it work as a variable virtual table in a measure.

CalcTable =
NATURALLEFTOUTERJOIN(
  CALCULATETABLE(
      ALLSELECTED('Activity Table'[PRODUCT_ID],'Activity Table'[CUSTOMER_ID]),
      'Activity Table'[ActivityTypeID]=14,
      NOT(ISBLANK('Activity Table'[PRODUCT_ID]))
  ),
  CALCULATETABLE(
      SUMMARIZE(
           'Activity Table',
           'Activity Table'[CUSTOMER_ID],
           'Activity Table'[ACTIVITY_DATE],
           'Activity Table'[ACTIVITY_USER_ID],
           'Activity Table'[PRODUCT_GROUP_ID],
           "prodCount",
           COUNTROWS('Activity Table')
      ),
      'Activity Table'[ActivityTypeID]=8
  )
)

Measure = 
SUMX(
  GROUPBY (
    CalcTable,
    CalcTable[CUSTOMER_ID], 
    CalcTable[ACTIVITY_USER_ID], 
    CalcTable[PRODUCT_GROUP_ID], 
    CalcTable[ACTIVITY_DATE],
    "ProductsPerCustomer",
    MAXX(CURRENTGROUP(), CalcTable[prodCount])
  ),
  [ProductsPerCustomer]
)

Re: Crossjoin, GENERATE?

datamodel — Sun, 13 Oct 2019 19:45:14 GMT

ActivityTypeID 8 is at the customer level, and count of all ActivityTypeID 8 should be applied to all products for that customer.

Activity Table

CustomerID	ActivityDate	ActivityTypeID	ProductID
123	2019-11-01	15	888
123	2019-11-01	8
123	2019-11-02	8
123	2019-11-03	8
123	2019-11-09	15	999

I'm actually facing another issue with the formula now. Say we add another activity 8 in December for this group

Activity Table

CustomerID	ActivityDate	ActivityTypeID	ProductID
123	2019-11-01	15	888
123	2019-11-01	8
123	2019-11-02	8
123	2019-11-03	8
123	2019-11-09	15	999
123	2019-12-01	8

If I break it down by Product, I'll get count of 4 for both 888 and 999 (desired). If I break it down by Customer, I also get count of 4 (desired). BUT if I break it down by month, I get a count of 4 for November.

The desired result is to get 3 for November and 1 for December. I'm missing a 'clear date filter' somewhere, but I can't figure out where.

ActivityCount = SUMX(
    GROUPBY (
        NATURALLEFTOUTERJOIN(
            CALCULATETABLE(SUMMARIZE('Activity Table','Activity Table'[PRODUCT_ID],'Activity Table'[CUSTOMER_ID]),NOT(ISBLANK('Activity Table'[PRODUCT_ID]))),
            SUMMARIZE(ALLSELECTED('Activity Table'),'Activity Table'[CUSTOMER_ID],"activityCount",CALCULATE(COUNTROWS('Activity Table'),'Activity Table'[ACTIVITY_TYPE]=8))
            ),
    'Activity Table'[CUSTOMER_ID],
    "AveragePerCustomer", MAXX ( CURRENTGROUP (), [activityCount] )
    ),
    [AveragePerCustomer]
)

Re: Crossjoin, GENERATE?

datamodel — Sat, 12 Oct 2019 15:28:09 GMT

Figured it out by summarizing ALLSELECTED activity. Thank you jdbuchanan71 for the 90% :)

ActivityCount = SUMX(
    GROUPBY (
        NATURALLEFTOUTERJOIN(
            CALCULATETABLE(SUMMARIZE('Activity Table','Activity Table'[PRODUCT_ID],'Activity Table'[CUSTOMER_ID]),NOT(ISBLANK('Activity Table'[PRODUCT_ID]))),
            SUMMARIZE(ALLSELECTED('Activity Table'),'Activity Table'[CUSTOMER_ID],"activityCount",CALCULATE(COUNTROWS('Activity Table'),'Activity Table'[ACTIVITY_TYPE]=8))
            ),
    'Activity Table'[CUSTOMER_ID],
    "AveragePerCustomer", MAXX ( CURRENTGROUP (), [activityCount] )
    ),
    [AveragePerCustomer]
)

Re: Crossjoin, GENERATE?

datamodel — Fri, 11 Oct 2019 15:49:48 GMT

Thank you, that helped. I modified the calc a bit to add it to a measure, instead of a new table. The below calc is able to get the correct total but the only issue is that it doesn't break it down by Product on the product dimension. It does however break it down by Customer.

ActivityCount = SUMX(
    GROUPBY (
        NATURALLEFTOUTERJOIN(
            CALCULATETABLE(SUMMARIZE('Activity Table','Activity Table'[PRODUCT_ID],'Activity Table'[CUSTOMER_ID]),NOT(ISBLANK('Activity Table'[PRODUCT_ID]))),
            SUMMARIZE('Activity Table','Activity Table'[CUSTOMER_ID],"activityCount",CALCULATE(COUNTROWS('Activity Table'),'Activity Table'[ACTIVITY_TYPE]=8))
            ),
    'Activity Table'[CUSTOMER_ID],
    "AveragePerCustomer", MAXX ( CURRENTGROUP (), [activityCount] )
    ),
    [AveragePerCustomer]
)

PRODUCT	ActivityCount
888	(missing value)
777	(missing value)
999	(missing value)
Total	7

CUSTOMER	ActivityCount
123	3
4567	2
78900	2
Total	7

Re: Crossjoin, GENERATE?

datamodel — Thu, 10 Oct 2019 17:56:24 GMT

That's the issue, productID is NOT on all rows, so I need a virtual table that will somehow crossjoin all customer/product combinations and do a count on that (but only where activitytype = 8). *I think*

Another crossjoin question

datamodel — Thu, 10 Oct 2019 17:48:58 GMT

In the following activity table, I would like to get a count of all products that interacted with eachother under activity types 7,8,and15.

CustomerID	ProductID	ActivityTypeID
123	888	7
123	888	8
123	789	15
4567	888	7
4567	777	15
4567	999	15
4567	999	8

(pivot on related table Product)

Product	ProductInteractions	(number of products interacted with across all customers)
888	3	(interacted with products 789 on customer 123, and 777 and 999 on customer 4567)
777	2	(interacted with products 888 on customer 4567 and 999 on customer 4567)
999	2	(interacted with products 777 on customer 4567 and 888 on customer 4567)
789	1	(interacted with products 888 on customer 123)

(pivot on related table Customer)

Customer	ProductInteractions	(number of products interacted for all customers)
123	2	(888 and 789 interacted with eachother)
4567	3	(888, 777, and 999 interacted with eachother)

(pivot on related tables Customer and Product)

Customer	Product	ProductInteractions	(number of products interacted with eachother at customer/product level)
123	888	1	(1 interaction for 789)
123	789	1	(1 interaction for 888)
4567	888	2	(1 interaction for 777 and 1 for 999)
4567	777	2	(1 interaction for 888 and 1 for 999)
4567	999	2	(1 interaction for 777 and 1 for 888)

Crossjoin, GENERATE?

datamodel — Thu, 10 Oct 2019 17:33:49 GMT

This is probably simple, but I just can't seem to figure it out.

Need a way to count all Activity Type = 8 by product by customer. In the activity below, both 888 and 999 products should get a count of 3.

Activity Table

CustomerID	ActivityDate	ActivityTypeID	ProductID
123	2019-11-01	15	888
123	2019-11-01	8
123	2019-11-02	8
123	2019-11-03	8
123	2019-11-09	15	999
4567	2019-11-01	15	777
4567	2019-11-01	8
4567	2019-11-02	8
78900	2019-11-02	15	777
78900	2019-11-06	8
78900	2019-11-07	8

Need measure ActivityCount with desired result below:

(pivot on related table Product)

PRODUCT	ActivityCount
888	3	(3 rows with activity type 8 for customer 123)
777	4	(4 rows with activity type 8 for customers 4567 and 78900)
999	3	(3 rows with activity type 8 for customer 123)

(pivot on related table Customer)

CUSTOMER	ActivityCount
123	3	(3 rows with activity type 8 for customer 123)
4567	2	(2 rows with activity type 8 for customer 4567)
78900	2	(2 rows with activity type 8 for customer 78900)

Re: What does this icon in Field List mean?

datamodel — Tue, 06 Jun 2017 18:10:33 GMT

I found another reference to this question. It appears to be an old icon from Excel imports... unique identifier of a table.

https://community.powerbi.com/t5/Desktop/Field-list-question/td-p/17312